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');