Payroll - Employes Whose Pics are Missing

SELECT   LAST_NAME,EMPLOYEE_NUMBER , NATIONAL_IDENTIFIER
  FROM   PER_ALL_PEOPLE_F PAPF, PER_IMAGES PI
 WHERE       PAPF.PERSON_ID = PI.PARENT_ID(+)
         AND IMAGE_ID IS NULL                    --AND LAST_NAME LIKE 'RUSHI%'
         AND CURRENT_EMPLOYEE_FLAG = 'Y'
         AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

GL - Period Wise Transaction Summary

/* FORMATTED ON 12/1/2011 11:50:10 AM (QP5 V5.114.809.3010) */
SELECT   B.NAME BATCH_NAME,
         B.DESCRIPTION BATCH_DESCRIPTION,
         B.RUNNING_TOTAL_ACCOUNTED_DR BATCH_TOTAL_DR,
         B.RUNNING_TOTAL_ACCOUNTED_CR BATCH_TOTAL_CR,
         B.STATUS BATCH_STATUS,
         B.DEFAULT_EFFECTIVE_DATE EFFECTIVE_DATE,
         B.DEFAULT_PERIOD_NAME BATCH_PERIOD_NAME,
         B.CREATION_DATE,
         U.USER_NAME BATCH_CREATED_BY,
         H.JE_CATEGORY,
         H.JE_SOURCE,
         H.PERIOD_NAME JE_PERIOD_NAME,
         H.NAME JOURNAL_NAME,
         H.STATUS JOURNAL_STATUS,
         H.CREATION_DATE JE_CREATED_DATE,
         U1.USER_NAME JE_CREATED_BY,
         H.DESCRIPTION JE_DESCRIPTION,
         H.RUNNING_TOTAL_ACCOUNTED_DR JE_TOTAL_DR,
         H.RUNNING_TOTAL_ACCOUNTED_CR JE_TOTAL_CR,
         L.JE_LINE_NUM LINE_NUMBER,
         L.LEDGER_ID,
         GLCC.CONCATENATED_SEGMENTS ACCOUNT,
         L.ENTERED_DR,
         L.ENTERED_CR,
         L.ACCOUNTED_DR,
         L.ACCOUNTED_CR,
         XLAL.UNROUNDED_ACCOUNTED_DR XLA_UNROUNDED_ACCOUNTED_DR,
         XLAL.UNROUNDED_ACCOUNTED_CR XLA_UNROUNDED_ACCOUNTED_CR,
         L.DESCRIPTION,
         XLAL.CODE_COMBINATION_ID,
         XLAL.ACCOUNTING_CLASS_CODE,
         XLAL.ACCOUNTED_DR XLAL_ACCOUNTED_DR,
         XLAL.ACCOUNTED_CR XLAL_ACCOUNTED_CR,
         XLAL.DESCRIPTION XLAL_DESCRIPTION,
         XLAL.ACCOUNTING_DATE XLAL_ACCOUNTING_DATE,
         XLATE.ENTITY_CODE XLATE_ENTITY_CODE,
         XLATE.SOURCE_ID_INT_1 XLATE_SOURCE_ID_INT_1,
         XLATE.SOURCE_ID_INT_2 XLATE_SOURCE_ID_INT_2,
         XLATE.SOURCE_ID_INT_3 XLATE_SOURCE_ID_INT_3,
         XLATE.SECURITY_ID_INT_1 XLATE_SECURITY_ID_INT_1,
         XLATE.SECURITY_ID_INT_2 XLATE_SECURITY_ID_INT_2,
         XLATE.TRANSACTION_NUMBER XLATE_TRANSACTION_NUMBER
  FROM   GL_JE_BATCHES B,
         GL_JE_HEADERS H,
         GL_JE_LINES L,
         FND_USER U,
         FND_USER U1,
         GL_CODE_COMBINATIONS_KFV GLCC,
         GL_IMPORT_REFERENCES GIR,
         XLA_AE_LINES XLAL,
         XLA_AE_HEADERS XLAH,
         XLA_EVENTS XLAE,
         XLA.XLA_TRANSACTION_ENTITIES XLATE
 -- , RCV_TRANSACTIONS RCVT
 WHERE       B.CREATED_BY = U.USER_ID
         AND H.CREATED_BY = U1.USER_ID
         AND B.JE_BATCH_ID = H.JE_BATCH_ID
         AND H.JE_HEADER_ID = L.JE_HEADER_ID
         AND XLAL.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
         AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
         AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
         AND GIR.GL_SL_LINK_TABLE = XLAL.GL_SL_LINK_TABLE
         AND GIR.GL_SL_LINK_ID = XLAL.GL_SL_LINK_ID
         AND XLAL.AE_HEADER_ID = XLAH.AE_HEADER_ID
         AND XLAH.EVENT_ID = XLAE.EVENT_ID
         AND XLAE.ENTITY_ID = XLATE.ENTITY_ID
         AND XLAE.APPLICATION_ID = XLATE.APPLICATION_ID
         -- AND XLATE.SOURCE_ID_INT_1 = RCVT.TRANSACTION_ID
         AND H.JE_SOURCE = 'Receivables'
         AND H.PERIOD_NAME = 'JUN-11'  --'&PERIOD_NAME'

Oracle General Ledeger Certification Stuff

Oracle General Ledeger Intracts With -
----------------------------------------

Oracle Payables sends invoices payments , realizes gain and loss on foreign currencies and invoice price variance to GL

Oracle Receivables sends invoices payments , adjustments,debit memos , credit memos, cash and realizaed gain and loss on foreign currencies and invoice price variance to GL


Oracle Assets sends capital and construction in process asset additions , cost adjustments , transfers , retirements , depreciations and reclassifications to GL

Oracle purchasing sends accruals or receipts not invoiced , purchase orders , final closes and cancellations to GL

Oracle property sends revenues and expenses related to teal estate to GL

Oracle HR shares employee information with GL

Oracle Payroll sends salary , deductions and tax information to GL

Oracle Inventory sends cycle counts , physical inventory adjustments , receiving transactions , delivery transactions , intercompany transfers , sales order issues , internal requsitions , sub-inventory transfers and COGS to GL

BI - Oracle Business Intelligence .

Intract with Subledgers
-----------------------------

Subledgers (by Oracle Subledger Accounting) > Gl_interface > Journal Import > Journals > Post > Gl_balances


4 C's in R12 GL
-----------------------------
Chart of account(COA) - Provides Account Code ,
Calendar - provides date and Period ,
Currency - Transaction currency and
Accounting Conversion - provides Accounting rules and regulations for the company/Country

JV Approval Stuck - NO_MANAGER_RESPONSE

SELECT A.*
FROM (SELECT (SELECT MAX (DOC_SEQUENCE_VALUE)
FROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.JE_BATCH_ID =
(SELECT REVERSE(SUBSTR (
REVERSE (ITEM_KEY),
6,
LENGTH (ITEM_KEY)
))
FROM DUAL))
DOC_SEQUENCE_VALUE,
(SELECT NAME
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID =
(SELECT REVERSE(SUBSTR (REVERSE (ITEM_KEY),
6,
LENGTH (ITEM_KEY)))
FROM DUAL))
BATCH_NAME,
(SELECT JE_BATCH_ID
FROM GL_JE_BATCHES
WHERE JE_BATCH_ID =
(SELECT REVERSE(SUBSTR (REVERSE (ITEM_KEY),
6,
LENGTH (ITEM_KEY)))
FROM DUAL))
JE_BATCH_ID,
MESSAGE_NAME,
STATUS,
TO_USER,
SUBJECT
FROM WF_NOTIFICATIONS B
WHERE MESSAGE_TYPE = 'GLBATCH'
AND MESSAGE_NAME = 'NO_MANAGER_RESPONSE'
AND STATUS = 'OPEN'
OR BEGIN_DATE LIKE TO_DATE ('09082005', 'dd/mm/yyyy')
OR B.RECIPIENT_ROLE LIKE '%Approver_name%') A,
GL_JE_BATCHES B
WHERE A.JE_BATCH_ID = B.JE_BATCH_ID AND B.STATUS != 'P'

FAQs About Journal Approval

Questions and Answers

Can the preparer of the journal batch approve the journal?

Yes, preparer can approve the journal when below two conditions are met
1. When the Journals: Allow Preparer Approval profile option is set to ‘Yes’
2. Preparer authorization limit is more than maximum absolute amount of journal batch

Preparer authorization limit can be obtained using below query

R11:
SELECT nvl(authorization_limit, 0)
FROM GL_AUTHORIZATION_LIMITS a, fnd_user b,gl_set_of_books c
WHERE a.employee_id = b.employee_id
AND a.set_of_books_id =c.set_of_books_id
And b.user_name=’&peparer_id’
And c.name=’&set_of_books_name’;

R12:

SELECT nvl(authorization_limit, 0)
FROM GL_AUTHORIZATION_LIMITS a, fnd_user b,gl_ledgers c
WHERE a.employee_id = b.employee_id
AND a.ledger_id =c.ledger_id
And b.user_name=’&preparer_id’
And c.name=’&ledger_name’;

Maximum line amount of journal batch can be found using below query

SELECT max(abs(nvl(JEL.accounted_dr, 0) – nvl(JEL.accounted_cr, 0)))
FROM GL_JE_LINES JEL,
GL_JE_HEADERS JEH,
GL_JE_BATCHES JEB
WHERE JEH.je_batch_id = JEB.je_batch_id
AND JEH.je_header_id = JEL.je_header_id
AND JEH.currency_code <> ‘STAT’
And JEB.name=&journal_batch_name;

Is preparer same as creator of journal batch?

No, Preparer is the one who initiates the journal batch approval process and he/she need not be the journal
batch creator.

How to find supervisor of the user and the authorization limit of the user and supervisor?

Use below query to get the user’s supervisor details and authorization limits of the user and his supervisor.
R11:
select a.user_name user_id ,b.full_name user_full_name,
c.d_supervisor_id supervisor_user_id ,
d.authorization_limit User_auth_limit ,
e.authorization_limit Supervisor_auth_limit
from
fnd_user a,
per_all_people_f b,
PER_ASSIGNMENTS_V7 c,
gl_authorization_limits_v d,
gl_authorization_limits_v e
Where a.employee_id=b.person_id
and b.person_id=c.person_id(+)
and a.employee_id=d.employee_id(+)
and c.supervisor_id=e.employee_id(+)
and a.user_name=’&preparer_id’;

R12:
select a.user_name user_id ,b.full_name user_full_name,
c.d_supervisor_id supervisor_user_id ,
d.authorization_limit User_auth_limit ,
e.authorization_limit Supervisor_auth_limit
from
fnd_user a,
per_all_people_f b,
PER_ASSIGNMENTS_V7 c,
gl_authorization_limits_v d,
gl_authorization_limits_v e
Where a.employee_id=b.person_id
and b.person_id=c.person_id(+)
and a.employee_id=d.employee_id(+)
and c.supervisor_id=e.employee_id(+)
and a.user_name=’&preparer_id’;

How to find all work flow notification details of specific journal batch?

Use below queries to find work flow activity status & notification details of the journal batch

select * from wf_notifications where notification_id in
(select notification_id from WF_ITEM_ACTIVITY_STATUSES where
item_type=’GLBATCH’ and item_key like ‘&je_batch_id%’ );

Select * from WF_ITEM_ACTIVITY_STATUSES where
item_type=’GLBATCH’ and item_key like ‘&je_batch_id%’;

je_batch_id can be obtained using below query
select je_batch_id from gl_je_batches where name=&je_batch_name;

What are different types of approver methods and how to set approver method?

There are 3 types of approver methods

1.Go Up Management Chain –Goes up in HRMS manager hierarchy
2.Go Direct —Goes directly to the person with authorization limit
3.One Stop Then Go Direct – Goes to the immediate manager then directly to the person with authorization limit

This has to be set using the profile option “Journals: Find Approver Method”

Payroll - Employee look up details extraction

SELECT PAPF.first_name||' '||PAPF.middle_names||' '||PAPF.last_name Emplyee_name
,PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
,TO_CHAR(PACT.EFFECTIVE_DATE,'MON-YY') MONTH
,SUBSTR(OPMTL.ORG_PAYMENT_METHOD_NAME,1,30) PAYMENT_METHOD
,OPM.CURRENCY_CODE PAYMENT_CURRENCY
,NVL(PERPAY.ATTRIBUTE1,PAPF.first_name||' '||PAPF.middle_names||' '||PAPF.last_name) CHEQUE_NAME
,PPT.PAYMENT_TYPE_NAME PAYMENT_TYPE
--,TARGET.SEGMENT1 BANK
,decode(TARGET.TERRITORY_CODE,'AE',(select meaning from hr_lookups h
WHERE h.lookup_type = 'AE_BANK_NAMES'
and h.application_id = 800
and h.enabled_flag = 'Y'
and lookup_code = TARGET.SEGMENT1),(select meaning from hr_lookups h
WHERE h.lookup_type = 'AE_BANK_NAMES'
and h.application_id = 800
and h.enabled_flag = 'Y'
and lookup_code = TARGET.SEGMENT1) --TARGET.SEGMENT1
) BANK
,decode(TARGET.TERRITORY_CODE,'AE',TARGET.SEGMENT4,TARGET.SEGMENT4) ACCOUNT_CODE
--,TARGET.SEGMENT2 BRANCH
,decode(TARGET.TERRITORY_CODE,'AE',(select meaning from hr_lookups h
WHERE LOOKUP_TYPE = 'AE_BRANCH_NAMES'
AND APPLICATION_ID = 800
AND ENABLED_FLAG = 'Y'
and lookup_code = TARGET.SEGMENT2), (select meaning from hr_lookups h
WHERE LOOKUP_TYPE = 'AE_BRANCH_NAMES'
AND APPLICATION_ID = 800
AND ENABLED_FLAG = 'Y'
and lookup_code = TARGET.SEGMENT2)--TARGET.SEGMENT2
) BRANCH
FROM PAY_EXTERNAL_ACCOUNTS TARGET
,PAY_PERSONAL_PAYMENT_METHODS_F PERPAY
, PAY_PRE_PAYMENTS PP
, PAY_ASSIGNMENT_ACTIONS ASSACT
, PER_ALL_ASSIGNMENTS_F PAAF
, PER_ALL_PEOPLE_F PAPF
, PAY_PAYROLL_ACTIONS PACT
, HR_LOOKUPS BANK
, PAY_ORG_PAYMENT_METHODS_F_TL OPMTL
, PAY_ORG_PAYMENT_METHODS_F OPM
, PAY_PAYMENT_TYPES PPT
, (SELECT employee_number, sum(pay_value) bonus, period_name
FROM xyka_earnings_deductions_v xedv
WHERE xedv.element_name = 'Sales Incentive'
AND xedv.period_name = :p_period_name
GROUP BY employee_number, period_name
) xedv
WHERE OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
AND ASSACT.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND OPM.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID
AND PACT.ACTION_TYPE IN ('P','U')
AND PP.ASSIGNMENT_ACTION_ID = ASSACT.ASSIGNMENT_ACTION_ID
AND ASSACT.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND PERPAY.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID
AND BANK.LOOKUP_CODE(+) = TARGET.SEGMENT1
AND BANK.LOOKUP_TYPE(+) = 'GB_BANKS'
AND PACT.EFFECTIVE_DATE BETWEEN PERPAY.EFFECTIVE_START_DATE AND PERPAY.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PACT.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE
AND PERPAY.PERSONAL_PAYMENT_METHOD_ID = PP.PERSONAL_PAYMENT_METHOD_ID
AND TARGET.EXTERNAL_ACCOUNT_ID(+) = PERPAY.EXTERNAL_ACCOUNT_ID
-- AND (ASSACT.PAYROLL_ACTION_ID NOT IN (SELECT X_PAYROLL_ACTION_ID FROM XYKA_PAY_BANK_TRANSFER)
-- OR ASSACT.ASSIGNMENT_ACTION_ID NOT IN (SELECT X_ASSIGNMENT_ACTION_ID FROM XYKA_PAY_BANK_TRANSFER))
AND TO_CHAR(PACT.EFFECTIVE_DATE,'MON-YY') =:p_period_name
AND PAPF.EMPLOYEE_NUMBER = xedv.employee_number(+)
AND PPT.PAYMENT_TYPE_NAME not in ('OM Cash','AE Cash')
ORDER BY PP.ASSIGNMENT_ACTION_ID

R12 AR Cash Receipts - By API

mo_global.set_policy_context ('S', j.x_inv_org_id);
ar_receipt_api_pub.create_cash
(p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => l_currency_code,
p_amount => j.amount,
p_receipt_number => j.receipt_number,
p_receipt_date => j.x_date,
p_gl_date => j.x_date,
p_customer_id => j.customer_id,
p_customer_site_use_id => j.bill_to_site_use_id,
p_org_id => j.x_inv_org_id,
p_remittance_bank_account_id => l_bank_account_use_id,
p_receipt_method_id => l_receipt_method_id,
p_cr_id => l_cash_receipt_id,
p_comments => j.comments
);



ar_receipt_api_pub.APPLY
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_cash_receipt_id => l_cash_receipt_id,
p_customer_trx_id => l.customer_trx_id,
p_applied_payment_schedule_id => l.payment_schedule_id,
--p_discount => l_discount_taken,
p_amount_applied => l.applied_amount,
--p_amount_applied_from => l_amt_applied_from,
p_org_id => j.x_inv_org_id,
p_apply_date => j.x_date,
p_apply_gl_date => j.x_date,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

Open Financials Receipts batch

DECLARE
/* to get ar debtor receipts */
CURSOR debtor_receipts
IS
SELECT x_header_id header_id,
x_date x_date,
x_outlet_code outlet_code,
x_division_code division_code,
x_receipt_number receipt_number,
x_cheque_number cheque_number,
x_bank_name bank_name,
x_receipt_source receipt_source,
x_payment_type payment_type,
x_customer_id customer_id,
customer_memo_line customer_memo_line,
x_bill_to_site_use_id bill_to_site_use_id,
x_set_of_books_id set_of_books_id,
x_org_id org_id,
x_gl_cc_id memo_cc_id,
x_memo_line_id memo_line_id,
x_amount amount,
x_comments comments,
x_org_id
FROM xyka_cash_debtor_recpt_hdr_v
WHERE x_ar_transferred_flag = 'N'
AND x_receipt_source = ('Customer')
AND x_gl_transferred_flag <> 'C'
AND x_set_of_books_id = '&1'
AND x_org_id = NVL ('&2', x_org_id)
AND x_date BETWEEN NVL (
TO_DATE ('&3', 'RRRR/MM/DD HH24:MI:SS'),
TRUNC (TO_DATE (SYSDATE, 'DD-MON-RRRR'))
)
AND NVL (
TO_DATE ('&4', 'RRRR/MM/DD HH24:MI:SS'),
TRUNC (
TO_DATE (SYSDATE, 'DD-MON-RRRR')
)
);


/* get receipt batch source based on receipt source */
CURSOR batch_source (
p_org_id NUMBER
)
IS
SELECT ABS.batch_source_id,
ABS.last_batch_num,
ABS.default_receipt_class_id,
ABS.default_receipt_method_id,
ABS.org_id,
arm.REMIT_BANK_ACCT_USE_ID,
aaa.bank_account_id
FROM ar_batch_sources_all ABS,
ar_receipt_method_accounts_all arm,
ce_bank_acct_uses_all aaa
WHERE ABS.default_receipt_method_id = arm.receipt_method_id
AND arm.remit_bank_acct_use_id = aaa.BANK_ACCT_USE_ID
AND UPPER (name) LIKE '%DEBTOR%RECEIPT%'
AND ABS.org_id = p_org_id;


/* to get applied invoices against receipts from debtor receipt */

CURSOR ar_invoices (p_header_id NUMBER)
IS
SELECT x_line_id line_id,
x_header_id header_id,
x_customer_trx_number trx_number,
x_customer_trx_id customer_trx_id,
x_payment_schedule_id payment_schedule_id,
x_applied_amount applied_amount,
x_due_date due_date,
x_org_id org_id
FROM xyka_cash_debtor_inv_lines
WHERE x_header_id = p_header_id;


-- define variables

--
t_user_id NUMBER := 0;
t_org_id NUMBER := 0;
t_login_id NUMBER := 0;
t_req_id NUMBER := 0;
t_date DATE := NULL;
t_message VARCHAR2 (32000) := NULL;
tot_rec NUMBER := 0;
tot_err_rec NUMBER := 0;
--

-- define user specified parameters

--
lv_rec_count NUMBER := 1;
lv_org_id NUMBER (15) := 0;
lv_valid_cnt NUMBER (15) := 0;
lv_null_cnt NUMBER (15) := 0;
lv_chart_ok BOOLEAN := FALSE;
lv_curr VARCHAR2 (15) := NULL;
lv_err_flag VARCHAR2 (1) := 'N';
l_batch_id NUMBER := 0;
l_cash_receipt_id NUMBER := 0;
l_batch_number NUMBER;
l_control_amt NUMBER := 0;
l_control_count NUMBER := 0;
l_set_of_books_id NUMBER := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
l_org_id NUMBER;
l_site_use_id NUMBER;
l_receipt_line_id NUMBER := 1;
l_batch_source_id NUMBER;
l_receipt_class_id NUMBER;
l_receipt_method_id NUMBER;
l_bank_branch_id NUMBER;
l_bank_account_use_id NUMBER;
l_customer_id NUMBER;
l_invoice_count NUMBER := 0;
l_source_id NUMBER;
l_amt_applied_from NUMBER;
l_discount_taken NUMBER;
l_currency_code VARCHAR2 (100);
--
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_count NUMBER;
l_msg_data_out VARCHAR2 (1000);
l_mesg VARCHAR2 (1000);
l_err_msg VARCHAR2 (1000);
p_count NUMBER;
--
l_receipt_number VARCHAR2 (40);
l_dr_amount NUMBER;
l_cr_amount NUMBER;
l_app_attribute_rec ar_receipt_api_pub.attribute_rec_type;
l_payment_number VARCHAR2 (20);
l_cr_id NUMBER;
-- ---------------------------------------------------------------------
BEGIN
-- Initializes the required variables
--fnd_global.apps_initialize (1111, 50680, 222);


DBMS_OUTPUT.put_line('Program XYKAARDR (AR Debtor Receipts batch) started at .....'
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));


BEGIN
SELECT currency_code
INTO l_currency_code
FROM gl_ledgers
WHERE ledger_id = l_set_of_books_id;
EXCEPTION
WHEN OTHERS
THEN
l_currency_code := NULL;
END;


FOR j IN debtor_receipts
LOOP
FOR s IN batch_source (j.org_id)
LOOP
l_batch_source_id := s.batch_source_id;
l_receipt_class_id := s.default_receipt_class_id;
l_receipt_method_id := s.default_receipt_method_id;
l_bank_account_use_id := s.REMIT_BANK_ACCT_USE_ID;
l_batch_number := s.last_batch_num;
END LOOP;

mo_global.set_policy_context ('S', j.org_id);

ar_receipt_api_pub.create_cash (
p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => l_currency_code,
p_amount => j.amount,
p_receipt_number => j.receipt_number,
p_receipt_date => j.x_date,
p_gl_date => j.x_date,
p_customer_id => j.customer_id,
p_customer_site_use_id => j.bill_to_site_use_id,
p_org_id => j.org_id,
p_remittance_bank_account_id => l_bank_account_use_id,
p_receipt_method_id => l_receipt_method_id,
p_cr_id => l_cash_receipt_id,
p_comments => j.comments
);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
ELSIF l_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_msg_data IS NULL
THEN
EXIT;
END IF;

DBMS_OUTPUT.put_line ('Message' || p_count || '.' || l_msg_data);
l_err_msg := l_err_msg || l_msg_data;
END LOOP;
END IF;

/* validates the result */
IF l_cash_receipt_id IS NULL
THEN
t_message :=
t_message
|| ' Receipt creation failed. Error message : '
|| l_err_msg;
ELSE
FOR l IN ar_invoices (j.header_id)
LOOP
IF l.trx_number <> 'On Account'
THEN
--l_amt_applied_from := NULL;
--l_discount_taken := 0;


/* calls the api to apply receipt against the invoice*/
ar_receipt_api_pub.apply (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_cash_receipt_id => l_cash_receipt_id,
p_customer_trx_id => l.customer_trx_id,
p_applied_payment_schedule_id => l.payment_schedule_id,
--p_discount => l_discount_taken,
p_amount_applied => l.applied_amount,
--p_amount_applied_from => l_amt_applied_from,
p_org_id => j.org_id,
p_apply_date => j.x_date,
p_apply_gl_date => j.x_date,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' || l_msg_data);
ELSIF l_msg_count > 1
THEN
LOOP
p_count := p_count + 1;
l_msg_data :=
fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_msg_data IS NULL
THEN
EXIT;
END IF;

t_message := p_count || '.' || l_msg_data;
END LOOP;
END IF;
END IF;
END LOOP;
END IF;

UPDATE XYKA_CASH_DEBTOR_RECPT_HDR
SET AR_CASH_RECEIPT_ID = l_cash_receipt_id,
x_ar_transferred_flag = 'Y'
WHERE x_header_id = j.header_id;

tot_rec := tot_rec + 1;
END LOOP;

COMMIT;


IF tot_rec = 0
THEN
DBMS_OUTPUT.put_line ('There is no record for Transfer.');
ELSE
DBMS_OUTPUT.put_line('Total records inserted for Program XYKAARDR_SH (Open Financials Receipts batch) ... :'
|| TO_CHAR (tot_rec));
END IF;

DBMS_OUTPUT.put_line('Program XYKAARDR_SH (Open Financials Receipts batch) '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
DBMS_OUTPUT.put_line (t_message);
END;
/

Dead Reservations

SELECT L.LINE_ID,
( SELECT ORDER_NUMBER FROM OE_ORDER_HEADERS_ALL WE WHERE WE.HEADER_ID = L.HEADER_ID) ORDER_NUMBER
, ORDERED_ITEM
, RESERVATION_QUANTITY
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.OPEN_FLAG,'Y')='N'
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = nvl('&OE_SOURCE_CODE',MTI.SOURCE_CODE))
AND NOT EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
AND WDD.SOURCE_CODE ='OE'
AND WDD.INV_INTERFACED_FLAG IN ('N','P')
AND WDD.RELEASED_STATUS <> 'D');

How to Find out where Diag file is created

SELECT value
FROM v$parameter
WHERE name = 'utl_file_dir';

Purchase Order Update

Purchase Order Getting stuck with “in process” status

Scenario:
Opened approved purchase order in edit mode, made couple of changes at PO header/Line level and submitted for approval but PO got stuck with “in process” status.
NO Pending workflow notification.
Solution:

Step1: Query purchase order data with correct po number.
SELECT hr.name,
poh.segment1,
poh.REVISION_NUM,
poh.wf_item_type,
poh.wf_item_key,
authorization_status,
poh.po_header_id
FROM po_headers_all poh, hr_all_organization_units hr
WHERE poh.org_id = hr.organization_id
AND poh.segment1 =

Step2: Update purchase order authorizing status to “REQUIRES REAPPROVAL”.
update po_headers_all
set authorization_status='REQUIRES REAPPROVAL'
where authorization_status ='IN PROCESS'
and poh.segment1 = ;

From front end Open PO , Unreserve Funds , Cancel PO , if Required .

UPDATE PO_HEADERS_ALL
SET AUTHORIZATION_STATUS = 'REJECTED'
WHERE AUTHORIZATION_STATUS = 'REQUIRES REAPPROVAL'
AND SEGMENT1 LIKE

commit;

Payroll : Employee Contract Information

SELECT
--PAPF.PERSON_ID PERSON_ID,
(SELECT NAME
FROM HR_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID =
(SELECT ORGANIZATION_ID
FROM APPS.PER_ALL_ASSIGNMENTS_F PAA
WHERE (NVL (SYSDATE, SYSDATE) BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE)
AND PERSON_ID = PAPF.PERSON_ID)) DEPARTMENT,
PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
PAPF.FULL_NAME EMPLOYEE_NAME,
trim(PAC.SEGMENT10) TELEPHONE_NUMBER
--to_char(PAPF.EFFECTIVE_START_DATE,'DD-MON-YY') JOINING_DATE
FROM
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_ANALYSES PPA,
PER_ANALYSIS_CRITERIA PAC,
FND_ID_FLEX_STRUCTURES FIFL,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_JOBS PJ
WHERE
PAPF.PERSON_ID = PPA.PERSON_ID
AND PAC.ANALYSIS_CRITERIA_ID = PPA.ANALYSIS_CRITERIA_ID
AND PAC.ID_FLEX_NUM = PPA.ID_FLEX_NUM
AND PAC.ID_FLEX_NUM = FIFL.ID_FLEX_NUM
AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND FIFL.ID_FLEX_STRUCTURE_CODE = 'RAR_SPECIAL_INFORMATION'
AND PAPF.PERSON_ID=PAAF.PERSON_ID
AND PAAF.PRIMARY_FLAG='Y'
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PJ.JOB_ID(+)=PAAF.JOB_ID
--GROUP by NAME , PAPF.PERSON_ID , PAPF.EMPLOYEE_NUMBER , PAPF.FULL_NAME , PAC.SEGMENT10 , PAPF.EFFECTIVE_START_DATE
order by 1

Script deletes from mtl_demand which are not in sync with Mtl_reservations

Execute On your Own Risk !!!!

SELECT V.PROFILE_OPTION_VALUE OE_PROFILE
FROM FND_PROFILE_OPTION_VALUES V
WHERE (V.PROFILE_OPTION_ID, V.APPLICATION_ID, V.LEVEL_ID) =
(SELECT V2.PROFILE_OPTION_ID,V2.APPLICATION_ID,MAX(V2.LEVEL_ID)
FROM FND_PROFILE_OPTIONS OO,
FND_PROFILE_OPTION_VALUES V2
WHERE ((V2.LEVEL_ID = 10001 AND V2.LEVEL_VALUE=0)
OR (V2.LEVEL_ID = 10002 AND V2.LEVEL_VALUE=660))
AND OO.PROFILE_OPTION_ID = V2.PROFILE_OPTION_ID
AND OO.APPLICATION_ID = V2.APPLICATION_ID
AND OO.APPLICATION_ID = 660
AND UPPER(OO.PROFILE_OPTION_NAME) = 'ONT_SOURCE_CODE'
GROUP BY V2.PROFILE_OPTION_ID, V2.APPLICATION_ID)
AND V.LEVEL_VALUE = DECODE(V.LEVEL_ID, 10001, 0, 10002, 660);

PROMPT ** This gets rid of extraneous demand and reservations that are left
PROMPT ** after a line has been cancelled

SELECT L.LINE_ID, L.HEADER_ID
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.CANCELLED_FLAG,'N')='Y'
AND L.CANCELLED_QUANTITY IS NOT NULL
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = '&OE_SOURCE_CODE');


UPDATE MTL_RESERVATIONS
SET PRIMARY_RESERVATION_QUANTITY = 0
, RESERVATION_QUANTITY = 0
, LAST_UPDATED_BY=-2471362
WHERE PRIMARY_RESERVATION_QUANTITY>0
AND EXISTS (SELECT 'X'
FROM OE_ORDER_LINES_ALL L
WHERE nvl(L.CANCELLED_FLAG,'N')='Y'
AND L.CANCELLED_QUANTITY IS NOT NULL
AND L.LINE_ID = MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = '&OE_SOURCE_CODE'));



PROMPT ** This gets rid of extraneous demand and reservations that are left
PROMPT ** after a line has been closed or delivery detail is cancelled

SELECT L.LINE_ID, L.HEADER_ID
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.OPEN_FLAG,'Y')='N'
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = '&OE_SOURCE_CODE')
AND NOT EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
AND WDD.SOURCE_CODE ='OE'
AND WDD.INV_INTERFACED_FLAG IN ('N','P')
AND WDD.RELEASED_STATUS <> 'D');


UPDATE MTL_RESERVATIONS
SET PRIMARY_RESERVATION_QUANTITY = 0
, RESERVATION_QUANTITY = 0
, LAST_UPDATED_BY=-2471362
WHERE PRIMARY_RESERVATION_QUANTITY>0
AND EXISTS (SELECT 'X'
FROM OE_ORDER_LINES_ALL L
WHERE nvl(L.OPEN_FLAG,'Y')='N'
AND L.LINE_ID = nvl(MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID,-99)
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = '&OE_SOURCE_CODE')
AND NOT EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
AND WDD.SOURCE_CODE ='OE'
AND WDD.INV_INTERFACED_FLAG IN ('N','P')
AND WDD.RELEASED_STATUS <> 'D'));



PROMPT ORDER DETAILS WITH ORPHAN RESERVATIONS AFTER LINE IS DELETED

SELECT MSO.SEGMENT1 ORD_NUMBER,
MSO.SEGMENT2 ORD_TYPE,
MSO.SALES_ORDER_ID sALES_ORDER_ID,
MR.DEMAND_SOURCE_LINE_ID oRDER_LINE_ID,
MR.iNVENTORY_ITEM_ID iTEM_ID,
MR.ORGANIZATION_ID ORGANIZATION_ID,
MR.PRIMARY_RESERVATION_QUANTITY pRSV_QTY,
MR.RESERVATION_QUANTITY RSV_QTY
FROM MTL_RESERVATIONS MR,
MTL_SALES_ORDERS MSO
WHERE MSO.SALES_ORDER_ID=MR.DEMAND_SOURCE_HEADER_ID
AND MR.DEMAND_SOURCE_TYPE_ID IN (2,8)
AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID );


PROMPT UPDATING MTL_RESERVATIONS

UPDATE MTL_RESERVATIONS MR
SET PRIMARY_RESERVATION_QUANTITY=0,
RESERVATION_QUANTITY=0,
LAST_UPDATED_BY=-2471362
WHERE MR.DEMAND_SOURCE_TYPE_ID IN (2,8)
AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID);
COMMIT;

PROMPT **Updating records for closed /cancelled lines in mtl_demand where records are not in sync
UPDATE MTL_DEMAND D
SET PRIMARY_UOM_QUANTITY = 0
, LINE_ITEM_QUANTITY = 0
, COMPLETED_QUANTITY = 0
, LINE_ITEM_RESERVATION_QTY = 0
, RESERVATION_QUANTITY = 0
, LAST_UPDATED_BY=-2471362
WHERE DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE = 2
AND DEMAND_SOURCE_LINE = ( SELECT O.LINE_ID
FROM OE_ORDER_LINES_ALL O
WHERE ( NVL(o.OPEN_FLAG,'Y') = 'N'
OR NVL(O.CANCELLED_FLAG,'N') ='Y')
AND O.LINE_ID =D.DEMAND_SOURCE_LINE )
AND DEMAND_SOURCE_LINE NOT IN ( select TRX_SOURCE_LINE_ID
FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.SOURCE_LINE_ID = D.DEMAND_SOURCE_LINE)
AND DEMAND_SOURCE_LINE NOT IN (SELECT SOURCE_LINE_ID
FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID=D.DEMAND_SOURCE_LINE
AND WDD.SOURCE_CODE='OE'
AND WDD.INV_INTERFACED_FLAG IN ('N','P'));

PROMPT FINAL UPDATING MTL_DEMAND

UPDATE MTL_DEMAND SET PRIMARY_UOM_QUANTITY=0,
COMPLETED_QUANTITY=0,
RESERVATION_QUANTITY=0,
LAST_UPDATED_BY=-2471362
WHERE DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE=2
AND DEMAND_ID IN (SELECT N_COLUMN1 FROM MTL_RESERVATIONS WHERE
DEMAND_SOURCE_TYPE_ID IN (2,8)
AND PRIMARY_RESERVATION_QUANTITY=0
AND RESERVATION_QUANTITY=0
AND LAST_UPDATED_BY=-2471362);



PROMPT **Deleting reservations which are complete

COMMIT;

DELETE FROM MTL_RESERVATIONS WHERE
DEMAND_SOURCE_TYPE_ID IN (2,8)
AND LAST_UPDATED_BY=-2471362;


PROMPT **Deleting records from mtl_demand where reservations are complete

DELETE FROM MTL_DEMAND WHERE
DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE=2
AND LAST_UPDATED_BY=-2471362;

spool off

COMMIT;

EXIT;

Inventory Exception Report - ( SQL*Plus- Fancy HTML )

/* Fancy HTML Inventory Exception Report - SQL*Plus for Missing Subinventory Assignments */


SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF;
SET PAGESIZE 200;
SET FEEDBACK OFF;
SET MARKUP HTML ENTMAP OFF;
DECLARE
L_ITEM_ID NUMBER;
L_ORGANIZATION_ID NUMBER := 0;
L_ITEM_COUNT NUMBER;
M_EXISTS VARCHAR2 (1);
L_ERR_FLAG CHAR (1) := 'N';
M_ITEM VARCHAR2 (240);
M_ORG_CODE VARCHAR2 (240);
M_SUBINV VARCHAR2 (240);

CURSOR C
IS
SELECT SEGMENT1, INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = '&1'
AND STOCK_ENABLED_FLAG = 'Y'
AND CREATION_DATE > '01-JUN-2011'; -- CUT OF DATE

CURSOR SUB_INVS (
P_ORGANIZATION_ID IN NUMBER
)
IS
SELECT DISTINCT SECONDARY_INVENTORY_NAME
FROM APPS.MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = '&1'
AND SECONDARY_INVENTORY_NAME IN ('ELECS-HA', 'STAGING');
BEGIN

-- fnd_file.put_line (fnd_file.LOG,'RUSHIKESH Log ');
-- FND_FILE.put_line(FND_FILE.output,'RUSHIKESH Output');

DBMS_OUTPUT.PUT_LINE('Report Run Date : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
DBMS_OUTPUT.PUT_LINE('ITEM SUB INVENTORY MISSIG ASSIGNMENT REPORT');
DBMS_OUTPUT.PUT_LINE('' ); L_ITEM_COUNT := 0; FOR R IN C LOOP L_ITEM_ID := NULL; L_ORGANIZATION_ID := NULL; IF (L_ERR_FLAG <> 'Y') THEN FOR R1 IN SUB_INVS (R.ORGANIZATION_ID) LOOP BEGIN -- CHECK IF THE ITEM- ORGANIZATION - SUBINVENTORY COMBINATION IS ALREADY EXISTING -- SELECT 'm' INTO M_EXISTS FROM APPS.MTL_ITEM_SUB_INVENTORIES WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID AND ORGANIZATION_ID = R.ORGANIZATION_ID AND SECONDARY_INVENTORY = R1.SECONDARY_INVENTORY_NAME; --DBMS_OUTPUT.PUT_LINE('INVENTORY_ITEM_ID :'||R.INVENTORY_ITEM_ID); --DBMS_OUTPUT.PUT_LINE('ORGANIZATION_ID :'||R.ORGANIZATION_ID); --DBMS_OUTPUT.PUT_LINE('SUB INVENTORY :'||R1.SECONDARY_INVENTORY_NAME); ----- IF THE COMBINATION DOESNOT EXIST THEN POPULATE TO THE SUBINVENTORY TABLE ---------------- EXCEPTION WHEN NO_DATA_FOUND THEN SELECT SEGMENT1 INTO M_ITEM FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID AND ORGANIZATION_ID = R.ORGANIZATION_ID; SELECT ORGANIZATION_CODE INTO M_ORG_CODE FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_ID = R.ORGANIZATION_ID; DBMS_OUTPUT.PUT_LINE( '' || '' || ''); L_ITEM_COUNT := L_ITEM_COUNT + 1; WHEN OTHERS THEN NULL; END; END LOOP; END IF; -- END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('

Item Code	Organization	Missing Subinventory
'|| M_ITEM ||'	' || M_ORG_CODE ||'	' || R1.SECONDARY_INVENTORY_NAME ||'

' );
END;
/

Item Defined But Sub inventory Not Assigned in Organization

DECLARE
L_ITEM_ID NUMBER;
L_ORGANIZATION_ID NUMBER := 0;
L_ITEM_COUNT NUMBER;
M_EXISTS VARCHAR2 (1);
L_ERR_FLAG CHAR (1) := 'N';
M_ITEM VARCHAR2 (240);
M_ORG_CODE VARCHAR2 (240);
M_SUBINV VARCHAR2 (240);

CURSOR C
IS
SELECT SEGMENT1, INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = 122 -- organization wise
AND STOCK_ENABLED_FLAG = 'Y'
AND CREATION_DATE > '01-JUN-2011'; -- CUT OF DATE

CURSOR SUB_INVS (
P_ORGANIZATION_ID IN NUMBER
)
IS
SELECT DISTINCT SECONDARY_INVENTORY_NAME
FROM APPS.MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = 122
AND SECONDARY_INVENTORY_NAME IN ('EL-HA', 'STAGING');
-- Subinventories defined for organization
BEGIN
DBMS_OUTPUT.PUT_LINE('Started populating interface tables at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
L_ITEM_COUNT := 0;

FOR R IN C
LOOP
L_ITEM_ID := NULL;
L_ORGANIZATION_ID := NULL;

IF (L_ERR_FLAG <> 'Y')
THEN
FOR R1 IN SUB_INVS (R.ORGANIZATION_ID)
LOOP
BEGIN
-- CHECK IF THE ITEM- ORGANIZATION - SUBINVENTORY COMBINATION IS ALREADY EXISTING --
SELECT 'm'
INTO M_EXISTS
FROM APPS.MTL_ITEM_SUB_INVENTORIES
WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = R.ORGANIZATION_ID
AND SECONDARY_INVENTORY = R1.SECONDARY_INVENTORY_NAME;

--DBMS_OUTPUT.PUT_LINE('INVENTORY_ITEM_ID :'||R.INVENTORY_ITEM_ID);
--DBMS_OUTPUT.PUT_LINE('ORGANIZATION_ID :'||R.ORGANIZATION_ID);
--DBMS_OUTPUT.PUT_LINE('SUB INVENTORY :'||R1.SECONDARY_INVENTORY_NAME);
----- IF THE COMBINATION DOESNOT EXIST THEN POPULATE TO THE SUBINVENTORY TABLE ----------------
SELECT SEGMENT1
INTO M_ITEM
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = R.ORGANIZATION_ID;

SELECT ORGANIZATION_CODE
INTO M_ORG_CODE
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = R.ORGANIZATION_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE( M_ITEM
|| '-'
|| M_ORG_CODE
|| '-'
|| R1.SECONDARY_INVENTORY_NAME);

/* Commented to Verify first
INSERT INTO APPS.MTL_ITEM_SUB_INVENTORIES (
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_PLANNING_CODE
)
VALUES (R.INVENTORY_ITEM_ID,
R.ORGANIZATION_ID,
R1.SECONDARY_INVENTORY_NAME,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
6);
COMMIT;

*/
L_ITEM_COUNT := L_ITEM_COUNT + 1;
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
-- END IF;
END LOOP;
END;

Where's My Journal Voucher JV Stuck ?

--------------------------------- Solution I -------------------------------------------

In this example, the workflow failed originally because the journal preparers supervisor did not have an employee name assigned to the user.
Depending on the case, the corrected data must be entered in the steps below:

1. Verify the Workflow process is not there

This can be verified in Workflow (monitor):

Workflow - Find Processes
Item Type = Journal Batch
Process Name = GL Journal Approval Process

Or:

select *
from wf_notifications b
where b.subject like '%ournal batch%' -- Journal Batch Name
or begin_date like to_date ('09082005','dd/mm/yyyy')
or b.recipient_role like '%Approver_name%'


Resolution could be one of the following solutions:-
1. Approve the batch:-

update gl_je_batches
set approval_status_code = 'J'
where je_batch_id = &batch_id

To automatically approve the batch, set the approval_status_code in GL_JE_BATCHES to 'A'. This
will make the batch immediately available for posting.

or

2. To send the batch back for approval through workflow, set the @approval_status_code to 'R'.

--------------------------------- Solution II -------------------------------------------

In this example, the workflow failed originally because the journal preparers supervisor did not have an employee name assigned to the user.
Depending on the case, the corrected data must be entered in the steps below:

Select the 'Workflow Administrator responsibility'.
Note that the Workflow Administrator Role must be assigned to the User or Responsibility that is doing this.

Open the 'Status Monitor' screen (Administrator Workflow > Status Monitor)

---------
Query the workflow in the current context. To know the parameter values to use,
(Journal Approval uses the following values:
the item_type is 'GLBATCH'.
the item_key value starts with the JE_BATCH_ID value from gl_je_batches table.
the user_key is the Journal Batch Name (this is case sensitive).
Use the following SQL to obtain the item_key and item_type for a Journal Batch
Approval workflow:

SELECT item_type, item_key
FROM wf_items
WHERE user_key = '';

The tables where you can find relevant data using the above references are:

GL_JE_BATCHES
WF_NOTIFICATIONS
WF_ITEMS
WF_ITEM_ACTIVITY_STATUSES
WF_PROCESS_ACTIVITIES
)

Select the workflow with error from the list and open the 'Activity History'.

---------
Select the failed activity and click the 'Update Attributes' button.

---------
Update the missing attributes of the failed activity, for example:
- preparer id = employee id from the fnd_user table (-1 shows that an assignment was missing)
- Approver Name = Preparer's supervisor's name (i.e.Person Name to be Attached for the supervisor User)
- Approver Display Name = Preparer's supervisor's name (i.e.Person Name to be Attached for the supervisor User)

---------
Select the errored out activity and click on the 'Rewind' button.
Clicking on rewind button opens a page listing all the completed activities of this workflow and gives you a choice to select the activity from where the workflow should be rewound.

Select the first activity of the workflow as the point from where it is to be rewound.
A confirmation was received that the workflow was rewound .

Requery the workflow to see the latest status.
The notification must have been sent to the approver.

Payroll GL Account wise Break Up - All Accounts

/* Payroll - Break Up for Different Accounts can be generated from following SQL */


SELECT FULL_NAME,
EMPLOYEE_NUMBER,
RESULT_VALUE,
REPORTING_NAME,
NAME,
COST_SEGMENTS,
COSTED_VALUE,
DEBIT_OR_CREDIT
FROM (SELECT PAP.FULL_NAME,
PAP.EMPLOYEE_NUMBER,
V.RESULT_VALUE,
E.REPORTING_NAME,
HORG.NAME,
PCAK.CONCATENATED_SEGMENTS COST_SEGMENTS,
PC.COSTED_VALUE,
PC.BALANCE_OR_COST,
PC.DEBIT_OR_CREDIT
FROM APPS.PAY_RUN_RESULT_VALUES V,
APPS.PAY_RUN_RESULTS R,
APPS.PAY_ELEMENT_TYPES_F E,
APPS.PAY_ASSIGNMENT_ACTIONS A,
APPS.pay_payroll_actions P,
APPS.HR_ALL_ORGANIZATION_UNITS HORG,
APPS.per_all_assignments_f PAA,
APPS.PAY_COST_ALLOCATION_KEYFLEX PCAK,
APPS.PAY_COSTS PC,
APPS.per_all_people_f PAP,
APPS.PAY_INPUT_VALUES_F PIV
WHERE R.ELEMENT_TYPE_ID = E.ELEMENT_TYPE_ID
AND V.RUN_RESULT_ID = R.RUN_RESULT_ID
--AND R.SOURCE_TYPE<>'E'
AND R.ASSIGNMENT_ACTION_ID = A.ASSIGNMENT_ACTION_ID
AND A.PAYROLL_ACTION_ID = P.PAYROLL_ACTION_ID
AND A.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND ('31-JUL-11' BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE)
AND PAA.PERSON_ID = PAP.PERSON_ID
AND ('31-JUL-11' BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE)
AND PAA.ORGANIZATION_ID = HORG.ORGANIZATION_ID
AND P.EFFECTIVE_DATE = '31-JUL-11'
AND PCAK.SEGMENT5 = :Account_code --51203
AND PC.COST_ALLOCATION_KEYFLEX_ID =
PCAK.COST_ALLOCATION_KEYFLEX_ID
AND R.RUN_RESULT_ID = PC.RUN_RESULT_ID
AND V.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.UOM = 'M'
AND P.PAYROLL_ID IN (01, 02, 03)) -- will change as per payroll id
WHERE RESULT_VALUE IS NOT NULL
--AND EMPLOYEE_NUMBER LIKE '5%'
GROUP BY FULL_NAME,
EMPLOYEE_NUMBER,
RESULT_VALUE,
REPORTING_NAME,
NAME,
COST_SEGMENTS,
COSTED_VALUE,
DEBIT_OR_CREDIT

Payroll GL Account wise Break Up

/* Payroll GL Account wise Break Up */
/* You have a GL account for payrole , one can sue this query to find out break up for accounts like staff deduction etc */

SELECT FULL_NAME,EMPLOYEE_NUMBER,RESULT_VALUE,REPORTING_NAME,NAME, COST_SEGMENTS,COSTED_VALUE,DEBIT_OR_CREDIT
FROM
(
SELECT PAP.FULL_NAME,PAP.EMPLOYEE_NUMBER,V.RESULT_VALUE,
E.REPORTING_NAME,HORG.NAME, PCAK.CONCATENATED_SEGMENTS COST_SEGMENTS, PC.COSTED_VALUE, PC.BALANCE_OR_COST, PC.DEBIT_OR_CREDIT
FROM
APPS.PAY_RUN_RESULT_VALUES V,
APPS.PAY_RUN_RESULTS R,
APPS.PAY_ELEMENT_TYPES_F E,
APPS.PAY_ASSIGNMENT_ACTIONS A,
APPS.pay_payroll_actions P,
APPS.HR_ALL_ORGANIZATION_UNITS HORG,
APPS.per_all_assignments_f PAA,
APPS.PAY_COST_ALLOCATION_KEYFLEX PCAK,
APPS.PAY_COSTS PC,
APPS.per_all_people_f PAP,
APPS.PAY_INPUT_VALUES_F PIV
WHERE R.ELEMENT_TYPE_ID=E.ELEMENT_TYPE_ID
AND V.RUN_RESULT_ID=R.RUN_RESULT_ID
--AND R.SOURCE_TYPE<>'E'
AND R.ASSIGNMENT_ACTION_ID=A.ASSIGNMENT_ACTION_ID
AND A.PAYROLL_ACTION_ID=P.PAYROLL_ACTION_ID
AND A.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID
AND (:P_EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE)
AND PAA.PERSON_ID=PAP.PERSON_ID
AND (:P_EFFECTIVE_DATE BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE)
AND PAA.ORGANIZATION_ID = HORG.ORGANIZATION_ID
AND P.EFFECTIVE_DATE= :P_EFFECTIVE_DATE
AND PCAK.SEGMENT5 = :P_ACCOUNT_NO
AND PC.COST_ALLOCATION_KEYFLEX_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID
AND R.RUN_RESULT_ID = PC.RUN_RESULT_ID
AND V.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.UOM = 'M'
AND p.PAYROLL_ID = 63 -- will chage as per your company setup
)
WHERE RESULT_VALUE IS NOT NULL
GROUP BY FULL_NAME,EMPLOYEE_NUMBER,RESULT_VALUE,REPORTING_NAME,NAME, COST_SEGMENTS,COSTED_VALUE,DEBIT_OR_CREDIT

R12 Customer Query

/* R12 Customer Query */

SELECT DISTINCT *
FROM HZ_PARTIES CUST,
HZ_CUST_ACCOUNTS CUST_ACCT,
HZ_CUST_ACCT_SITES_ALL CUST_SITE,
HZ_CUST_SITE_USES_ALL CUST_USES,
HZ_LOCATIONS CUST_LOC
WHERE CUST_ACCT.CUST_ACCOUNT_ID = CUST_SITE.CUST_ACCOUNT_ID
AND CUST_SITE.CUST_ACCT_SITE_ID = CUST_USES.CUST_ACCT_SITE_ID
--AND CUST_USES.SITE_USE_ID = EXT_PAYER.ACCT_SITE_USE_ID
AND CUST_USES.LOCATION = CUST_LOC.LOCATION_ID(+)
AND CUST.PARTY_ID = CUST_ACCT.PARTY_ID
--AND PARTY_NAME LIKE '2%'
AND CUST_ACCT.CUST_ACCOUNT_ID = 20657 --(OE_ORDER_HEADERS_ALL.SOLD_TO_ORG_ID)

Inventory Opening Balance Interface (On Hand Qty Interface) Continue to Item Create

--- Create a Temp Table
-- Upload data from user xls to temp table
-- run a cursor to plae data from temp table
REM +=======================================================================+
REM | |
REM | |
REM +=======================================================================+
REM | FILENAME |
REM | |
REM | |
REM | DESCRIPTION |
REM | This script has been created to Uplaod Inventory opening balances |
REM | from legacy system to Oracle 11i/R12 inventory |
REM | |
REM | 1. Update User XLS data into an TEMP table |
REM | 2. Run a cursor to move data from temp table to pre int table |
REM | 3. Run a plsql block to migrate data from |
REM | pre_interface_table to oracle std interface table |
REM | 4. Launch Transaction manager from Inventory |
REM +=======================================================================+



CREATE TABLE APPS.AEO_ITM_LOAD_TMP
(
PART_CODE VARCHAR2(240 BYTE),
DIVISION VARCHAR2(3 BYTE),
PARTS_NAME VARCHAR2(240 BYTE),
STOCK_RECD NUMBER,
BALANCE NUMBER,
ISSUE_DATE DATE,
UNIT_PRICE NUMBER,
BRAND VARCHAR2(240 BYTE),
PRODUCT VARCHAR2(240 BYTE),
MODEL_NO VARCHAR2(240 BYTE)
)

COMMIT;

-- Onhand 22 @ rate of 50 , and sysdate-50 is last issue date

INSERT INTO AEO_ITM_LOAD_TMP
VALUES ('RARDE00093',
777,
'RAR Switch - Ignition',
22,
22,
SYSDATE - 50,
50,
'Daewoo ',
NULL,
NULL)

SELECT * FROM APPS.AEO_ITM_LOAD_TMP

------------------- Upload XLS data in Above created Table ------------------------

-- Create a pre interface table

CREATE TABLE UET_INV_OPENING_BALANCES_INT
(
DIVISION_CODE VARCHAR2(30 BYTE),
INVENTORY_ORG_CODE VARCHAR2(9 BYTE),
SUBINVENTORY_NAME VARCHAR2(15 BYTE),
ITEM_CODE VARCHAR2(90 BYTE),
PRIMARY_UOM_CODE VARCHAR2(9 BYTE),
QTY NUMBER,
UNIT_COST NUMBER,
VALUE NUMBER,
STOCK_LOCATOR VARCHAR2(75 BYTE),
LOT_NUMBER VARCHAR2(90 BYTE),
EXPIRY_DATE DATE,
LAST_ISSUE_DATE DATE,
LAST_RECEIPT_DATE DATE,
LAST_SUPPLIER_NO VARCHAR2(120 BYTE),
X_OB_IMPORTED CHAR(1 BYTE)
)


DELETE FROM UETDT.UET_INV_OPENING_BALANCES_INT

COMMIT;

-- Move data from TMP table to pre interface table with validations

DECLARE
N NUMBER;

CURSOR C1
IS
SELECT * FROM AEO_ITM_LOAD_TMP;
BEGIN
FOR REC IN C1
LOOP
INSERT INTO UETDT.UET_INV_OPENING_BALANCES_INT (DIVISION_CODE,
INVENTORY_ORG_CODE,
SUBINVENTORY_NAME,
ITEM_CODE,
PRIMARY_UOM_CODE,
QTY,
UNIT_COST,
VALUE,
STOCK_LOCATOR,
LOT_NUMBER,
EXPIRY_DATE,
LAST_ISSUE_DATE,
LAST_RECEIPT_DATE,
LAST_SUPPLIER_NO,
X_OB_IMPORTED)
VALUES (REC.DIVISION,
426, ----- INVENTORY_ORG_CODE
'ELECTRONIC',
REC.PART_CODE,
'PCE',
REC.BALANCE,
REC.UNIT_PRICE,
REC.BALANCE * REC.UNIT_PRICE,
'SPARES',
NULL,
NULL,
REC.ISSUE_DATE,
REC.ISSUE_DATE,
REC.BRAND,
'N');

COMMIT;

dbms_output.put_line('Started populating Inventory Opening Balances at TEMP Table : '|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));

END LOOP;

END;


SELECT * FROM UETDT.UET_INV_OPENING_BALANCES_INT

------------------------ Populating Inventory Opening Balances -------------------------------

-- Material acount is Receipt account not COGS

DECLARE
L_TRANSACTION_INTERFACE_ID NUMBER;
L_ITEM_COUNT NUMBER := 0;
L_EXP_COUNT NUMBER := 0;

CURSOR ITEM_C
IS
SELECT X.ITEM_CODE OLD_ITEM_CODE, X.QTY QUANTITY, X.UNIT_COST COST, X.LAST_ISSUE_DATE,
X.LAST_RECEIPT_DATE, X.LAST_SUPPLIER_NO, X.ITEM_CODE, X.EXPIRY_DATE LAST_EXP_DATE,
X.VALUE, MSI.ORGANIZATION_ID, MSI.RESTRICT_LOCATORS_CODE,
MSIF.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
GET_LOCATOR_ID(MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, X.STOCK_LOCATOR) LOCATOR_ID,
53322 MATERIAL_ACCOUNT, MSI.PRIMARY_UOM_CODE, MSI.INVENTORY_ITEM_ID,
P.DEFAULT_COST_GROUP_ID COST_GROUP_ID
FROM UETDT.UET_INV_OPENING_BALANCES_INT X,
MTL_SYSTEM_ITEMS MSI,
MTL_SECONDARY_INVENTORIES_FK_V MSIF,
MTL_PARAMETERS P
WHERE LTRIM (RTRIM (ITEM_CODE)) = MSI.SEGMENT1
AND MSI.ORGANIZATION_ID = P.ORGANIZATION_ID (+)
AND MSI.ORGANIZATION_ID = 426 --342,343
AND MSI.ORGANIZATION_ID = MSIF.ORGANIZATION_ID
AND LTRIM(RTRIM(X.DIVISION_CODE))='777'
AND X.INVENTORY_ORG_CODE='426'
AND MSIF.LOCATOR_TYPE = 5
AND X.QTY > 0
AND X.ITEM_CODE !='15AMPS-PLUGS';

-- 46789 COGS
/* MAIN PROGRAM */
BEGIN
DBMS_OUTPUT.PUT_LINE ( 'Started populating Inventory Opening Balances at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);

FOR R IN ITEM_C
LOOP
SELECT XUET_TRAN_INTERFACE_ID_S.NEXTVAL
INTO L_TRANSACTION_INTERFACE_ID
FROM DUAL;

BEGIN
/* INSERTING INTO MAIN TRANSACTION TABLE */
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(SOURCE_CODE, SOURCE_LINE_ID,
SOURCE_HEADER_ID, COST_GROUP_ID, PROCESS_FLAG, TRANSACTION_MODE,
TRANSACTION_INTERFACE_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
SUBINVENTORY_CODE, TRANSACTION_QUANTITY, TRANSACTION_UOM,
TRANSACTION_DATE, TRANSACTION_SOURCE_ID, TRANSACTION_TYPE_ID,
TRANSACTION_COST, TRANSACTION_REFERENCE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, LOCATOR_ID
)
VALUES (1, XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL,
XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL, R.COST_GROUP_ID, 1, 3,
L_TRANSACTION_INTERFACE_ID, R.INVENTORY_ITEM_ID, R.ORGANIZATION_ID,
R.SUBINVENTORY_CODE, TO_NUMBER (R.QUANTITY), R.PRIMARY_UOM_CODE,
'02-AUG-2011', R.MATERIAL_ACCOUNT, 40,
TO_NUMBER (R.COST), 'OPENING BALANCE', SYSDATE, -1,
-1, SYSDATE, -1, R.LAST_EXP_DATE, R.LAST_ISSUE_DATE,
R.LAST_RECEIPT_DATE, R.LAST_SUPPLIER_NO, R.LOCATOR_ID
);

-- COMMIT;

/* UPDATES THE PROCESS FLAG TO 'Y' */
UPDATE UETDT.UET_INV_OPENING_BALANCES_INT
SET X_OB_IMPORTED = 'Y'
WHERE ITEM_CODE = R.ITEM_CODE AND QTY = R.QUANTITY AND UNIT_COST = R.COST
AND VALUE = R.VALUE;

-- COMMIT;
L_ITEM_COUNT := L_ITEM_COUNT + 1;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Exception..');
END;
END LOOP;

DBMS_OUTPUT.PUT_LINE ('Number of normal items processed ' || L_ITEM_COUNT);
DBMS_OUTPUT.PUT_LINE ( 'Populating Inventory Opening Balances Completed at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
END;


COMMIT;

SELECT * FROM MTL_TRANSACTIONS_INTERFACE WHERE SOURCE_CODE = '1'


Go to inventory Setup > Transactions > Interface Managers > Run Material transaction ( Launch )

Item Import and Sub Inventory Assignment

Oracle Item Import and Sub Inventory Assignment

Create a Temp table ( Attribute1 represents Supplier Id )

Column Name Null? Data Type
SEGMENT1 Y VARCHAR2 (40 Byte)
SEGMENT2 Y VARCHAR2 (40 Byte)
DESCRIPTION Y VARCHAR2 (240 Byte)
ORGANIZATION_ID N NUMBER
PRIMARY_UOM_CODE Y VARCHAR2 (3 Byte)
ATTRIBUTE1 Y VARCHAR2 (240 Byte)

/* Insert Test Data into Table */

insert into aeo_item_import values ('RARDE00093','551','RAR Switch - Ignition',423,'PCE',214658) ;


commit ;

/* Insert into interface Table
Keep a eye on Item Template_id its predefined template id */

INSERT INTO apps.mtl_system_items_interface
( process_flag,transaction_type,set_process_id,segment1, segment2,
attribute1,organization_id, description,primary_uom_code ,template_id,created_by,
creation_date, last_update_date, last_updated_by
)
(select '1' ,'CREATE','0', segment1,segment2, attribute1,organization_id,
description,primary_uom_code,1, -1, SYSDATE, SYSDATE, -1
from aeo_item_import );

COMMIT;

Go to Oracle Inventory Items > Import > Import Item

Check Item is Created and assigned to 423 (organization_id which is Master Org )

Now we want to assign item to Child Org .

repeat above all steps just change organization_id from 423 to child organizations_id

--------------------------------------------------------------
/* Assigning Sub inventories to Items Created just now */

DECLARE
l_item_id NUMBER;
l_item_count NUMBER;
l_vendor_id NUMBER := 0;
l_err_flag CHAR (1) := 'N';
l_organization_id NUMBER := 0;
m_exists varchar2 (1);

/* Selecting Sub Inventories from custom table for populated items */
-- Select the items that have been enabled through the mass enable script ----
CURSOR c
IS
SELECT SEGMENT1,
SEGMENT2,
INVENTORY_ITEM_ID,
ORGANIZATION_ID
FROM apps.MTL_SYSTEM_ITEMS
WHERE segment1 IN (SELECT segment1 FROM aeo_item_import)
AND--AND trunc(creation_date) = trunc(sysdate)
ORGANIZATION_ID = 423;
-- 423 is organization_id for which you want to Insert sub inventories

CURSOR sub_invs (p_organization_id IN NUMBER)
IS
SELECT DISTINCT secondary_inventory_name
FROM apps.mtl_secondary_inventories
WHERE organization_id = 425; -- Master Org
/* ---- Main Program ---- */
BEGIN
DBMS_OUTPUT.put_line('======================================================================');
DBMS_OUTPUT.put_line('Started populating interface tables at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
l_item_count := 0;

FOR r IN c
LOOP
l_item_id := NULL;
l_vendor_id := NULL;
l_err_flag := 'N';
l_organization_id := NULL;

IF (l_err_flag <> 'Y')
THEN
FOR r1 IN sub_invs (r.organization_id)
LOOP
BEGIN
BEGIN
-- Check if the item- organization - subinventory combination is already existing --
SELECT 'm'
INTO m_exists
FROM apps.mtl_item_sub_inventories
WHERE inventory_item_id = r.inventory_item_id
AND organization_id = r.organization_id
AND secondary_inventory =
r1.secondary_inventory_name;
--dbms_output.put_line('Inventory_item_ID :'||r.inventory_item_id);
--dbms_output.put_line('Organization_ID :'||r.organization_id);
--dbms_output.put_line('Sub Inventory :'||r1.secondary_inventory_name);
----- If the combination doesnot exist then populate to the subinventory table ----------------
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- DBMS_OUTPUT.PUT_LINE(r.inventory_item_id || '-' || r.organization_id||'-'||r1.secondary_inventory_name);
INSERT INTO apps.mtl_item_sub_inventories (
inventory_item_id,
organization_id,
secondary_inventory,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_planning_code
)
VALUES (r.inventory_item_id,
r.ORGANIZATION_ID,
r1.secondary_inventory_name,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
6);

-- COMMIT; -- Committing the Insert --
l_item_count := l_item_count + 1;

/* Update the Flag */
UPDATE apps.mtl_system_items
SET attribute15 = 'Y'
WHERE inventory_item_id = r.inventory_item_id
AND organization_id = r.organization_id;
-- COMMIT; -- Comitting the Update --
END; -- End of Check procedure ---
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
-- END IF;
END LOOP;

COMMIT;
DBMS_OUTPUT.put_line (
'Total Records populated into Item Sub-Inventories : ' || l_item_count
);
DBMS_OUTPUT.put_line('Completed populating Item Sub-Inventories at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
DBMS_OUTPUT.put_line('======================================================================');
END;

OM - Order Hold and Release

/* A common war between Credit and Warehouse Dept is Delivery after Hold removal
Following query will put a focus on Order and hold till releasing a hold */

SELECT H.ORDER_NUMBER,
HO.NAME HOLD_NAME,
HS.HOLD_UNTIL_DATE,
HS.HOLD_COMMENT,
OH.HEADER_ID,
OH.LINE_ID,
OH.ORDER_HOLD_ID,
L.ITEM_IDENTIFIER_TYPE,
L.INVENTORY_ITEM_ID,
L.ORDERED_ITEM,
OHR.RELEASE_COMMENT
FROM OE_ORDER_HOLDS_ALL OH,
OE_ORDER_LINES_ALL L,
OE_ORDER_HEADERS_ALL H,
OE_HOLD_DEFINITIONS HO,
OE_HOLD_SOURCES_ALL HS,
OE_HOLD_RELEASES OHR
WHERE OH.HEADER_ID = H.HEADER_ID
AND (H.CANCELLED_FLAG IS NULL OR H.CANCELLED_FLAG = 'N')
--AND OH.RELEASED_FLAG != 'Y'
AND H.OPEN_FLAG = 'Y'
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND HS.HOLD_ID = HO.HOLD_ID
AND H.HEADER_ID = L.HEADER_ID(+)
AND L.OPEN_FLAG = 'Y'
AND L.LINE_ID = NVL (OH.LINE_ID, L.LINE_ID)
AND L.SERVICE_REFERENCE_LINE_ID IS NULL
AND OH.HOLD_RELEASE_ID IS NULL
--AND NVL(H.ORG_ID,0) = 204
AND NVL (L.ORG_ID, 0) = NVL (H.ORG_ID, 0)
AND OH.HOLD_RELEASE_ID = OHR.HOLD_RELEASE_ID(+)
ORDER BY HO.NAME, H.ORDER_NUMBER

/* When - Why */

SELECT H.ORDER_NUMBER,
RELEASE_REASON_CODE,
RELEASE_COMMENT,
TO_CHAR (OH.CREATION_DATE, 'DD-MON-YYYY hh24:mm:ss') HOLD_APPLIED_DATE,
TO_CHAR (OHR.CREATION_DATE, 'DD-MON-YYYY hh24:mm:ss') HOLD_RELEASE_DATE
FROM OE_ORDER_HOLDS_ALL OH, OE_HOLD_RELEASES OHR, OE_ORDER_HEADERS_ALL H
WHERE OH.HEADER_ID = H.HEADER_ID
AND OH.HOLD_RELEASE_ID = OHR.HOLD_RELEASE_ID
AND OHR.CREATION_DATE LIKE SYSDATE

Inventory Opening Balance Interface (On Hand Qty Interface Part - II An Example )

/* Create a preinterface Table */

CREATE TABLE UETDT.UET_INV_OPENING_BALANCES_INT
(
DIVISION_CODE VARCHAR2(30 BYTE),
INVENTORY_ORG_CODE VARCHAR2(9 BYTE),
SUBINVENTORY_NAME VARCHAR2(15 BYTE),
ITEM_CODE VARCHAR2(90 BYTE),
PRIMARY_UOM_CODE VARCHAR2(9 BYTE),
QTY NUMBER,
UNIT_COST NUMBER,
VALUE NUMBER,
STOCK_LOCATOR VARCHAR2(75 BYTE),
LOT_NUMBER VARCHAR2(90 BYTE),
EXPIRY_DATE DATE,
LAST_ISSUE_DATE DATE,
LAST_RECEIPT_DATE DATE,
LAST_SUPPLIER_NO VARCHAR2(120 BYTE),
X_OB_IMPORTED CHAR(1 BYTE)
)


/* Insert data into Pre-Interface Table
Note : Item is already created , assigned and Item sub inventory also added to item */
INSERT INTO UET_INV_OPENING_BALANCES_INT (DIVISION_CODE,
INVENTORY_ORG_CODE,
SUBINVENTORY_NAME,
ITEM_CODE,
PRIMARY_UOM_CODE,
QTY,
UNIT_COST,
VALUE,
STOCK_LOCATOR,
LOT_NUMBER,
EXPIRY_DATE,
LAST_ISSUE_DATE,
LAST_RECEIPT_DATE,
LAST_SUPPLIER_NO,
X_OB_IMPORTED)
VALUES (251,
707,
'ELECTRONICS',
'RAR GEMS', -- item already created
'EA',
21, -- Qty
0,
0,
'OUTLET', -- Locator
NULL,
SYSDATE,
SYSDATE - 10,
SYSDATE - 11,
0,
'Y');

COMMIT;


SELECT * FROM UET_INV_OPENING_BALANCES_INT

/* Create Custom Sequences to be used for interface header */

CREATE SEQUENCE APPS.XUET_TRAN_SOURCE_LINE_IDS_S
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
CACHE 20
NOCYCLE
NOORDER


CREATE SEQUENCE APPS.XUET_TRAN_INTERFACE_ID_S
INCREMENT BY 1
MINVALUE 1
NOMAXVALUE
CACHE 20
NOCYCLE
NOORDER

/* Procedure Starts */
----
SET SERVEROUTPUT ON SIZE 1000000;
----

/* Variables Declaration */

DECLARE
l_transaction_interface_id NUMBER;
l_item_count NUMBER := 0;
l_exp_count NUMBER := 0;

CURSOR item_c
IS
SELECT x.item_code old_item_code, x.qty quantity, x.unit_cost COST, x.last_issue_date,
x.last_receipt_date, x.last_supplier_no, x.item_code, x.expiry_date last_exp_date,
x.VALUE, msi.organization_id, msi.restrict_locators_code,
msif.secondary_inventory_name subinventory_code,
get_locator_id(msi.organization_id, msi.inventory_item_id, x.stock_locator) locator_id,
30598 material_account, msi.primary_uom_code, msi.inventory_item_id,
p.default_cost_group_id cost_group_id
FROM uetdt.uet_inv_opening_balances_int x,
mtl_system_items msi,
mtl_secondary_inventories_fk_v msif,
mtl_parameters p
WHERE LTRIM (RTRIM (item_code)) = msi.segment1
AND msi.organization_id = p.organization_id (+)
AND msi.organization_id = 343 --342,343
AND msi.organization_id = msif.organization_id
AND ltrim(rtrim(x.division_CODE))='251'
AND x.inventory_org_code='707'
AND msif.locator_type = 5
AND x.qty > 0;

/* Material account is hard coded which is COGS attached to item. */

/* Main Program */
BEGIN
DBMS_OUTPUT.put_line ( 'Started populating Inventory Opening Balances at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);

FOR r IN item_c
LOOP
SELECT XUET_TRAN_INTERFACE_ID_S.NEXTVAL
INTO l_transaction_interface_id
FROM DUAL;

BEGIN
/* Inserting into Main transaction table */
INSERT INTO mtl_transactions_interface
(source_code, source_line_id,
source_header_id, cost_group_id, process_flag, transaction_mode,
transaction_interface_id, inventory_item_id, organization_id,
subinventory_code, transaction_quantity, transaction_uom,
transaction_date, transaction_source_id, transaction_type_id,
transaction_cost, transaction_reference, last_update_date, last_updated_by,
created_by, creation_date, last_update_login, attribute12, attribute13,
attribute14, attribute15, locator_id
)
VALUES (1, XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL,
XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL, r.cost_group_id, 1, 3,
l_transaction_interface_id, r.inventory_item_id, r.organization_id,
r.subinventory_code, TO_NUMBER (r.quantity), r.primary_uom_code,
'25-JUL-2011', r.material_account, 40,
TO_NUMBER (r.COST), 'OPENING BALANCE', SYSDATE, -1,
-1, SYSDATE, -1, r.last_exp_date, r.last_issue_date,
r.last_receipt_date, r.last_supplier_no, r.locator_id
);

-- COMMIT;

/* Updates the Process flag to 'Y' */
UPDATE uetdt.uet_inv_opening_balances_int
SET x_ob_imported = 'Y'
WHERE item_code = r.item_code AND qty = r.quantity AND unit_cost = r.COST
AND VALUE = r.VALUE;

-- COMMIT;
l_item_count := l_item_count + 1;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Exception..');
END;
END LOOP;

DBMS_OUTPUT.put_line ('Number of normal items processed ' || l_item_count);
DBMS_OUTPUT.put_line ( 'Populating Inventory Opening Balances Completed at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
END;



SELECT * FROM MTL_TRANSACTIONS_INTERFACE

/* Go to Inventory > Setups > Transaction > Interface Manager
select Material transaction manager > Tools Launch Manager
In case of Error
Inventory > Transactions > Transaction Open Interface Query for ERROR
Select Submit > correct error field
resubmit Material transaction manager */

Possible Errors - Wrong Distribution Account , Invalid Sub inventory , Not a valid Inventory open period found .

Inventory Opening Balance Interface (On Hand Qty Interface)

This interface lets you import the on hand inventory into Oracle.

Interface tables:

MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)

Concurrent Program:

Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base Tables:

MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS

Validations:

Validate organization_id
Check if item is assigned to organization
Validate disposition_id
Check if the item for the org is lot controlled before inserting into the Lots interface table.
Check if the item for the org is serial controlled before inserting into Serial interface table.
Check if inventory already exists for that item in that org and for a lot.
Validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.

Some important columns that need to be populated in the interface tables:

MTL_TRANSACTIONS_INTERFACE:

TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker to explicitly process set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
TRANSACTION_SOURCE_ID
Account --- GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias --- MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule ---WIP_ENTITIES.WIP_ENTITY_ID
Sales Order --- MTL_SALES_ORDERS.SALES_ORDER_ID
ITEM_SEGMENT1 TO 20,
TRANSACTION_QTY,
TRANSACTION_UOM,
SUBINVENTORY_CODE,
ORGANIZATION_ID,
LOC_SEGMENT1 TO 20.

MTL_TRANSACTION_LOTS_INTERFACE:

TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)

MTL_SERIAL_NUMBERS_INTERFACE:

TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
VENDOR_SERIAL_NUMBER

Account Payable - Creating AP Invoice , CM - Credit Memo , DM

Account Payable - AP Invoice , CM - Credit Memo , DM - Debit Memo .
A very common requirement is creating AP INV, CM, DM using interface. lets have a look.

Scenario - Data for Invoices , CM ,DM from legacy system to Oracle Application.In common case companies multiple ERP's but want to maintain GL in Oracle.

Step 1 - Lets create a temp table in which we will dump data from legacy system.table contains all required columns for interface.

Considering Vendor and site is already created in Oracle.

CREATE TABLE APPS.KER_AP_INVOICES_NOV10_3
(
INVOICE_ID NUMBER(15) NOT NULL,
INVOICE_NUM VARCHAR2(50 BYTE) NOT NULL,
INVOICE_DATE DATE,
TERMS_DATE DATE,
INVOICE_TYPE VARCHAR2(25 BYTE),
VENDOR_ID NUMBER(15) NOT NULL,
VENDOR_SITE_ID NUMBER(15),
INVOICE_CURRENCY_CODE VARCHAR2(15 BYTE) NOT NULL,
INVOICE_AMOUNT NUMBER,
BALANCE_AMOUNT NUMBER,
EXCHANGE_RATE NUMBER,
EXCHANGE_RATE_TYPE VARCHAR2(30 BYTE),
EXCHANGE_DATE DATE,
BALANCE_AMOUNT_BD NUMBER,
DIVISION CHAR(3 BYTE),
REFERENCE VARCHAR2(240 BYTE),
VOUCHER_NUMBER NUMBER,
GL_DATE DATE NOT NULL,
VENDOR_NUMBER VARCHAR2(30 BYTE),
VENDOR_NAME VARCHAR2(240 BYTE)
) ;

Sample Data -
INVOICE_ID = 252665
INVOICE_NUM = A0012107.47
INVOICE_DATE = 10/12/2010
TERMS_DATE = 11/11/2010
INVOICE_TYPE = STANDARD
VENDOR_ID = 777
VENDOR_SITE_ID = 974
INVOICE_CURRENCY_CODE = EUR
INVOICE_AMOUNT = 1195.58
BALANCE_AMOUNT = 1195.58
EXCHANGE_RATE = 0.51
EXCHANGE_RATE_TYPE = User
EXCHANGE_DATE = 10/24/2010
BALANCE_AMOUNT_BD = 609.7458
DIVISION = 104 (DFF)
REFERENCE = DR.201002341
VOUCHER_NUMBER = 92009193
GL_DATE = 10/24/2010
VENDOR_NUMBER = 100777
VENDOR_NAME = IVECO S.P.A

Lets Talk About Oracle Receivables R12 - AR FAQ

1. What is TCA? Tables?
A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.
HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:
Organization for example, Oracle Corporation
Person for example, Jane Doe
Group for example, World Wide Web Consortium
Relationship for example, Jane Doe at Oracle Corporation.

HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.

HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_CONTACT_POINTS etc.

/* A Simple Customer Query : cust_acct.customer_number is 11i party number */

select distinct *
from hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
where cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
--AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust_uses.location = cust_loc.location_id(+)
AND cust.party_id = cust_acct.party_id
and PARTY_NAME like '2%'

2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?
A) Customer Conversion:
Interface Tables : RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL,
RA_CONTACT_PHONES_INT_ALL,
RA_CUSTOMER_BANKS_INT_ALL,
RA_CUST_PAY_METHOD_INT_ALL
Base Tables : RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,
RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc
B) Auto Invoice:
Interface Tables : RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL, RA_INTERFACE_ERRORS_ALL
Base Tables : RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL
C) AutoLockBox :
Interface Tables : AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables : AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
: AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables : AR_CASH_RECEIPTS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)


3. What are the tables in which Invoices/transactions information is stored?
A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.

RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).

RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.

The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.

The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.


4. What are the tables In which Receipt information is stored?
A)
AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.
Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.

AR_CASH_RECEIPTS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.

AR_RECEIVABLE_APPLICATIONS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.

5. What are the tables in which Accounts information is stored?
RA_CUST_TRX_LINE_GL_DIST_ALL

6. What are the different statuses for Receipts?
A) Unidentified – Lack of Customer Information
Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)
Applied – When all the required information is provided.
On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.
7. What Customization that you have done for Autolockbox?
- In Progress

8. What is Autolockbox?
A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps
• Import (Formats data from bank file and populates the Interface Table),
• Validation(Validates the data and then Populates data into Interim Tables),
• Post Quick Cash(Applies Receipts and updates Balances in BaseTables).
9. What is Transmission Format?
A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.

10. What is Auto Invoice?
A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.
Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.

11. What are the Mandatory Interface Tables in Auto Invoice?
RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.

12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?
A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.

13. What is AutoAccounting?
A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.

14. What are Autocash rules?
A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.
15. What are Grouping Rules? (Used by Autoinvoice)

A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.

16. What are Line Ordering Rules? (Used by Autoinvoice)
A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.

17. In which table you can see the amount due of a customer?
A) AR_PAYMENT_SCHEDULES_ALL

18. How do you tie Credit Memo to the Invoice?
At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.

19. What are the available Key Flex Fields in Oracle Receivables?
A) Sales Tax Location Flex field, It’s used for sales tax calculations.
Territory Flex field is used for capturing address information.

20. What are Transaction types? Types of Transactions in AR?
A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.