PAY Pay Advice

Description
Categories: BI Publisher
Imported from BI Publisher
Description: GB Pay Advice Report
Application: Payroll
Source: Pay Advice (XML)
Short Name: PAYGBSOE_XML
DB package: PAY_PAYGBSOE_XMLP_PKG
Run PAY Pay Advice and other Oracle EBS reports with Blitz Report™ on our demo environment
select paa.ASSIGNMENT_ID,
           paa.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID1,
           paa.PERSON_ID,
           paa.JOB_TITLE,
           paa.ASSIGNMENT_NUMBER,
           paa.NI_NUMBER,
           paa.LOCATION_ID,
           paa.INTERNAL_ADDRESS_LINE,
           paa. EXPENSE_CHECK_SEND_TO_ADDRESS ,
           paa.SALARY, 
          paa.DATE_EARNED,
           paa.PAYROLL_ACTION_ID,
           paa.PAY_BASIS,
           paa.RUN_ASSIGNMENT_ACTION_ID,
           paa.RUN_EFFECTIVE_DATE,
           paa.RUN_PAYROLL_ACTION_ID,
           paa.TIME_PERIOD_ID,
           paa.PAYROLL_ID,
           paa.PAY_ADVICE_DATE,
           paa.LAST_NAME,
           paa.INITIALS,
           Initcap(paa.TITLE) Title ,
           rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER1_dup),27)  SEGMENT1C ,
           rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER2_dup),27)  SEGMENT2C,
           rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER3_dup),27)  SEGMENT3C,
           rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER4_dup),27)  SEGMENT4C,
           rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER5_dup),27)  SEGMENT5C,
           rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER6_dup),27)  SEGMENT6C, 
    PAY_PAYGBSOE_XMLP_PKG.c_nameformula(Initcap ( paa.TITLE ), paa.INITIALS, paa.LAST_NAME) C_NAME, 
	--&c_output2 C_GET_ADDRESS, 
	--&c_output C_FF, 
PAY_PAYGBSOE_XMLP_PKG.populate_fields(paa. EXPENSE_CHECK_SEND_TO_ADDRESS , paa.PERSON_ID ,
 rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER1_dup_1),27),
rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER1_dup_2),27),
 rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER1_dup_3),27),
rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER1_dup_4),27),
rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER1_dup_5),27),
 rpad(Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER1_dup_6),27)
 ) C_GET_ADDRESS,
PAY_PAYGBSOE_XMLP_PKG.get_ff_data(paa.RUN_EFFECTIVE_DATE,paa.DATE_EARNED,paa.ASSIGNMENT_ID,paa.RUN_ASSIGNMENT_ACTION_ID,paa.PAYROLL_ACTION_ID,:p_bus_grp_id )C_FF,
	PAY_PAYGBSOE_XMLP_PKG.c_amount_paidformula(:C_PAYMENT_TOTAL, :C_DEDUCTION_TOTAL) C_AMOUNT_PAID, 
	PAY_PAYGBSOE_XMLP_PKG.cf_euro_amountformula(:C_AMOUNT_PAID) CF_EURO_AMOUNT, 
	PAY_PAYGBSOE_XMLP_PKG.segment1() C_SEG_NAME1, 
	PAY_PAYGBSOE_XMLP_PKG.segment2() C_SEG_NAME2, 
	PAY_PAYGBSOE_XMLP_PKG.segment3() C_SEG_NAME3, 
	PAY_PAYGBSOE_XMLP_PKG.segment4() C_SEG_NAME4, 
	PAY_PAYGBSOE_XMLP_PKG.segment5() C_SEG_NAME5, 
	PAY_PAYGBSOE_XMLP_PKG.segment6() C_SEG_NAME6,
	PAY_PAYGBSOE_XMLP_PKG.C_ADDRESS1_p C_ADDRESS1,
	PAY_PAYGBSOE_XMLP_PKG.C_ADDRESS2_p C_ADDRESS2,
	PAY_PAYGBSOE_XMLP_PKG.C_ADDRESS3_p C_ADDRESS3,
	PAY_PAYGBSOE_XMLP_PKG.C_REGION1_p C_REGION1,
	PAY_PAYGBSOE_XMLP_PKG.C_REGION2_p C_REGION2,
	PAY_PAYGBSOE_XMLP_PKG.C_REGION3_p C_REGION3,
	PAY_PAYGBSOE_XMLP_PKG.C_ACCOUNT_NO_p C_ACCOUNT_NO,
	PAY_PAYGBSOE_XMLP_PKG.C_TOWN_p C_TOWN,
	PAY_PAYGBSOE_XMLP_PKG.C_ANNUAL_SALARY_p C_ANNUAL_SALARY,
	to_char(PAY_PAYGBSOE_XMLP_PKG.C_PAY_DATE_p,'YYYY-MM-DD') C_PAY_DATE,
	PAY_PAYGBSOE_XMLP_PKG.C_TAX_PERIOD_p C_TAX_PERIOD,
	PAY_PAYGBSOE_XMLP_PKG.C_TAX_REFERENCE_NO_p C_TAX_REFERENCE_NO,
	PAY_PAYGBSOE_XMLP_PKG.C_TAX_CODE_p C_TAX_CODE,
	PAY_PAYGBSOE_XMLP_PKG.C_TAX_BASIS_p C_TAX_BASIS,
	PAY_PAYGBSOE_XMLP_PKG.C_NI_CATEGORY_p C_NI_CATEGORY,
	PAY_PAYGBSOE_XMLP_PKG.C_TAX_TEL_NO_p C_TAX_TEL_NO,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R1_TXT_p C_BALANCE_R1_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R1_VAL_p C_BALANCE_R1_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R2_TXT_p C_BALANCE_R2_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R2_VAL_p C_BALANCE_R2_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R3_TXT_p C_BALANCE_R3_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R3_VAL_p C_BALANCE_R3_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R4_TXT_p C_BALANCE_R4_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R4_VAL_p C_BALANCE_R4_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R5_TXT_p C_BALANCE_R5_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R5_VAL_p C_BALANCE_R5_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R6_TXT_p C_BALANCE_R6_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R6_VAL_p C_BALANCE_R6_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R7_TXT_p C_BALANCE_R7_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R7_VAL_p C_BALANCE_R7_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R8_TXT_p C_BALANCE_R8_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R8_VAL_p C_BALANCE_R8_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R9_TXT_p C_BALANCE_R9_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R9_VAL_p C_BALANCE_R9_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R10_TXT_p C_BALANCE_R10_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R10_VAL_p C_BALANCE_R10_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R11_TXT_p C_BALANCE_R11_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R11_VAL_p C_BALANCE_R11_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R12_TXT_p C_BALANCE_R12_TXT,
	PAY_PAYGBSOE_XMLP_PKG.C_BALANCE_R12_VAL_p C_BALANCE_R12_VAL,
	PAY_PAYGBSOE_XMLP_PKG.C_OUTPUT2_p C_OUTPUT2,
	PAY_PAYGBSOE_XMLP_PKG.C_OUTPUT_p C_OUTPUT
from   pay_gb_assignment_actions_v paa
where PAYROLL_ID=:P_PAYROLL_ID 
AND time_period_id=:P_TIME_PERIOD_ID 
AND paa.PAY_ADVICE_DATE =
NVL(fnd_date.displaydate_to_date(substr(:P_PAY_ADVICE_DATE,1,                                          length(fnd_date.date_to_displaydate(sysdate))))                                                   ,paa.PAY_ADVICE_DATE)
AND paa.ASSIGNMENT_ID  =  NVL(:P_ASSIGNMENT_ID, paa.ASSIGNMENT_ID) 
ORDER BY  DECODE(EXPENSE_CHECK_SEND_TO_ADDRESS,'H','H','O'),
           Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER1_dup) ,
           Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER2_dup),
           Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER3_dup),
           Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER4_dup),
           Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER5_dup),
           Decode(expense_check_send_to_address,'H',Null,&P_SORT_ORDER6_dup),
          &P_SORT_ORDER7
Parameter Name SQL text Validation
Payroll
 
LOV Oracle
Period
 
LOV Oracle
Pay Advice Date
 
LOV Oracle
Assignment Number
 
LOV Oracle
Sort Order 1
 
LOV Oracle
Sort Order 2
 
LOV Oracle
Sort Order 3
 
LOV Oracle
Sort Order 4
 
LOV Oracle
Sort Order 5
 
LOV Oracle
Sort Order 6
 
LOV Oracle
Sort Order 7
 
LOV Oracle
Blitz Report™