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