[Home] [Help]
1: PACKAGE BODY PA_PAY_INTERFACE AS
2: -- $Header: PAPAYIMPB.pls 120.39 2012/02/17 08:52:31 arbandyo noship $
3:
4: PROCEDURE DERIVE_PRIORITY ;
5: PROCEDURE convert_to_fc ;
99:
100: BEGIN
101: --FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(sysdate,'HH:MI:SS: ')|| p_message);
102: IF (p_message_type = 1 ) THEN
103: pa_debug.write_file('LOG', 'pa.plsql.PA_PAY_INTERFACE : '|| p_message , 1);
104: ELSIF ((p_message_type = 2) AND ( G_DEBUG_MODE = 'Y') ) THEN
105: pa_debug.write_file('LOG', 'pa.plsql.PA_PAY_INTERFACE : '|| p_message , 1);
106: END IF;
107:
101: --FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(sysdate,'HH:MI:SS: ')|| p_message);
102: IF (p_message_type = 1 ) THEN
103: pa_debug.write_file('LOG', 'pa.plsql.PA_PAY_INTERFACE : '|| p_message , 1);
104: ELSIF ((p_message_type = 2) AND ( G_DEBUG_MODE = 'Y') ) THEN
105: pa_debug.write_file('LOG', 'pa.plsql.PA_PAY_INTERFACE : '|| p_message , 1);
106: END IF;
107:
108: EXCEPTION /* When exception occurs, program needs to be aborted. */
109: WHEN OTHERS THEN
143: PPIT.TIME_PERIOD_ID ,
144: PPIT.SOURCE_START_DATE, /* bug 12690224*/
145: PPIT.SOURCE_END_DATE
146: FROM
147: PA_PAY_INTERFACE_TEMP PPIT
148: ORDER BY
149: PAYROLL_ACTION_ID
150:
151: )
203: X.TIME_PERIOD_START_DATE ,
204: X.TIME_PERIOD_END_DATE ,
205: X.SOURCE_START_DATE , /* bug 12690224*/
206: X.SOURCE_END_DATE,
207: PA_PAY_INTERFACE_RUN_ID_S.NEXTVAL,
208: X.TIME_PERIOD_ID ,
209: 'Y' ,
210: L_RUN_SEQUENCE ,
211: L_MAX_RUN_ID ,
291: X.PAYROLL_ID ,
292: 'EXT' ,
293: X.TIME_PERIOD_START_DATE ,
294: X.TIME_PERIOD_END_DATE ,
295: PA_PAY_INTERFACE_RUN_ID_S.NEXTVAL,
296: X.TIME_PERIOD_ID ,
297: 'Y' ,
298: L_RUN_SEQUENCE ,
299: L_MAX_RUN_ID ,
362: TIME_PERIOD_ID,
363: TIME_PERIOD_END_DATE,
364: ELEMENT_TYPE_ID
365: from
366: PA_PAY_INTERFACE_TEMP ) PIT,
367: PA_PAY_ELEMENT_MAPPING PEM
368: WHERE
369: PAA.BATCH_ID = PIT.PAYROLL_ACTION_ID
370: AND PAA.PAYROLL_ID = PIT.PAYROLL_ID
566: /*
567: Procedure : LOAD_PA_PROJ_TEMP
568: Type : Private
569: Purpose :
570: Below procedure loads the payroll data to a GTT PA_PAY_INTERFACE_TEMP.
571: The is done so as to avoid acessing the Payroll tables for each employee.
572: */
573:
574:
583: BEGIN
584:
585:
586: INSERT
587: INTO PA_PAY_INTERFACE_TEMP
588: (
589: COST_TYPE,
590: EFFECTIVE_DATE,
591: BUSINESS_GROUP_ID,
876: l_sql_sel_random := l_sql_sel_random || ' SUM(PIT.DEBIT_AMOUNT- PIT.CREDIT_AMOUNT) AMOUNT';
877:
878: l_sql_from_where :=
879: ' FROM PA_PAY_AUDIT PAA,
880: PA_PAY_INTERFACE_TEMP PIT,
881: PA_PAY_ELEMENT_MAPPING PEM
882: WHERE PAA.INTERFACE_RUN_ID = :C_INTERFACE_RUN_ID
883: AND PAA.BATCH_ID = PIT.PAYROLL_ACTION_ID
884: AND PAA.PAYROLL_ID = PIT.PAYROLL_ID
3793: G_USER_ID LAST_UPDATED_BY ,
3794: G_RUN_DATE CREATION_DATE ,
3795: G_USER_ID CREATED_BY
3796: FROM
3797: PA_PAY_INTERFACE_TEMP TEMP, PA_PAY_AUDIT AUD
3798: WHERE
3799: AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3800: -- TEMP.TIME_PERIOD_ID = AUD.TIME_PERIOD_ID AND
3801: TRUNC(NVL(TEMP.SOURCE_START_DATE,TEMP.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
5602: AND EI2.EXPENDITURE_ID = E.EXPENDITURE_ID
5603: AND EI2.EXPENDITURE_ITEM_DATE BETWEEN Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
5604: AND Trunc( NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
5605: AND EI.EXPENDITURE_ITEM_ID = nvl(EI2.ADJUSTED_EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_ID)
5606: AND PA_PAY_INTERFACE.GET_COST_METHOD1(EI2.EXPENDITURE_ID,EI2.JOB_ID,EI2.ORG_ID,
5607: NVL(EI2.OVERRIDE_to_ORGANIZATION_ID
5608: ,E.INCURRED_BY_ORGANIZATION_ID),EI2.EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_DATE) = 'ACTUAL'
5609: AND EXISTS (
5610: SELECT 1 FROM PA_EXPENDITURE_ITEMS EI3
5631: AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5632: AND EI2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5633: AND EI2.EXPENDITURE_ID = E.EXPENDITURE_ID
5634: AND EI2.EXPENDITURE_ITEM_DATE BETWEEN Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND Trunc( Nvl(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
5635: AND pa_pay_interface.get_cost_method(EI2.EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_DATE) = 'ACTUAL'
5636: AND NVL(EI2.NET_ZERO_ADJUSTMENT_FLAG,'N') = 'N'
5637: AND EI2.SYSTEM_LINKAGE_FUNCTION IN ('ST','OT')
5638: AND NVL(EI2.TRANSACTION_SOURCE,' ') <>'ORACLE TIME AND LABOR'
5639: )
5674: AND EI3.EXPENDITURE_ITEM_DATE BETWEEN
5675: Trunc(Nvl(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE))
5676: AND Trunc( NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE))
5677: AND
5678: PA_PAY_INTERFACE.GET_COST_METHOD1(EI3.EXPENDITURE_ID,EI3.JOB_ID,EI3.ORG_ID,
5679: NVL(EI3.OVERRIDE_to_ORGANIZATION_ID
5680: ,E.INCURRED_BY_ORGANIZATION_ID),EI3.EXPENDITURE_ITEM_ID,EI3.EXPENDITURE_ITEM_DATE)
5681: = 'ACTUAL'
5682: AND Ei3.INTERFACE_RUN_ID IS NULL
5863: SRC.PAY_ELEMENT_TYPE_ID ,
5864: 'Y' STATUS
5865: FROM
5866: PA_PAY_AUDIT AUD ,
5867: PA_PAY_INTERFACE_TEMP TEMP ,
5868: PA_PAY_SOURCE_AMOUNTS SRC
5869: WHERE
5870: AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
5871: AUD.INTERFACE_RUN_ID = SRC.INTERFACE_RUN_ID AND
5881: TEMP.ELEMENT_TYPE_ID PAY_ELEMENT_TYPE_ID ,
5882: 'Z' STATUS
5883: FROM
5884: PA_PAY_AUDIT AUD ,
5885: PA_PAY_INTERFACE_TEMP TEMP
5886: WHERE
5887: AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
5888: TRUNC(NVL(TEMP.SOURCE_START_DATE,TEMP.TIME_PERIOD_START_DATE)) = TRUNC(NVL(AUD.SOURCE_START_DATE,AUD.PAY_PERIOD_START_DATE)) AND
5889: TRUNC(NVL(TEMP.SOURCE_END_DATE,TEMP.TIME_PERIOD_END_DATE)) = TRUNC(NVL(AUD.SOURCE_END_DATE,AUD.PAY_PERIOD_END_DATE)) AND /* bug 12690224*/
6014: G_LOGIN_ID,
6015: G_REQUEST_ID ,
6016: ASSIGNMENT_ACTION_ID
6017: FROM
6018: PA_PAY_INTERFACE_TEMP TEMP ,
6019: PA_PAY_AUDIT AUD
6020: WHERE
6021: AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
6022: TEMP.PAYROLL_ACTION_ID = AUD.BATCH_ID AND