[Home] [Help]
1766: /*
1767: Procedure : GET_PERSON_TC
1768: Type : Private
1769: Purpose : Loads the eligible timecards of the Employee for the Pay period
1770: into a GTT PA_PAY_EXP_TEMP. This is done to reduce the hits on PA_EXPENDITURE_ITEMS
1771: in further processing.
1772: During distribution of pay elements , we would refer to only PA_PAY_EXP_TEMP
1773:
1774: */
1768: Type : Private
1769: Purpose : Loads the eligible timecards of the Employee for the Pay period
1770: into a GTT PA_PAY_EXP_TEMP. This is done to reduce the hits on PA_EXPENDITURE_ITEMS
1771: in further processing.
1772: During distribution of pay elements , we would refer to only PA_PAY_EXP_TEMP
1773:
1774: */
1775:
1776: PROCEDURE get_person_TC
1785: write_log(Log, 'Loading EI ' || ' between ' || P_START_DATE || ' and ' || P_END_DATE);
1786:
1787:
1788: DELETE
1789: FROM PA_PAY_EXP_TEMP ;
1790:
1791:
1792: INSERT
1793: INTO PA_PAY_EXP_TEMP
1789: FROM PA_PAY_EXP_TEMP ;
1790:
1791:
1792: INSERT
1793: INTO PA_PAY_EXP_TEMP
1794: (
1795: INCURRED_BY_PERSON_ID,
1796: EXPENDITURE_ITEM_ID ,
1797: EXPENDITURE_ITEM_DATE,
1861: -- (
1862: -- SELECT temp.expenditure_item_id,
1863: -- ei.acct_currency_code,
1864: -- ei.acct_raw_cost
1865: -- FROM PA_PAY_EXP_TEMP temp ,
1866: -- pa_expenditure_items_all ei
1867: -- WHERE (
1868: -- ( /* uncosted ei picked up for stamping cost on that*/
1869: -- temp.payroll_accrual_flag IS NULL
1890: -- )
1891:
1892: -- LOOP
1893:
1894: -- UPDATE PA_PAY_EXP_TEMP t
1895: -- SET ORIG_CURR_CODE = x.acct_currency_code
1896: -- , ORIG_AMOUNT = x.acct_raw_cost
1897: -- WHERE t.expenditure_item_id = x.expenditure_item_id;
1898:
1904: (
1905: SELECT temp.expenditure_item_id,
1906: ei1.acct_currency_code,
1907: ei1.acct_raw_cost
1908: FROM PA_PAY_EXP_TEMP temp ,
1909: pa_expenditure_items_all ei1,
1910: pa_expenditure_items_all ei2,
1911: pa_expenditure_items_all ei3
1912: WHERE
1925: )
1926:
1927: LOOP
1928:
1929: UPDATE PA_PAY_EXP_TEMP t
1930: SET ORIG_CURR_CODE = x.acct_currency_code
1931: , ORIG_AMOUNT = x.acct_raw_cost
1932: WHERE t.expenditure_item_id = x.expenditure_item_id;
1933:
1934: END LOOP;
1935:
1936: */
1937: /* Bug 12779047 Above logic re-written below */
1938: UPDATE PA_PAY_EXP_TEMP T
1939: SET
1940: (
1941: ORIG_CURR_CODE ,
1942: ORIG_AMOUNT
1963: )
1964: WHERE EXPENDITURE_ITEM_ID IN
1965: (SELECT T.EXPENDITURE_ITEM_ID
1966: FROM PA_EXPENDITURE_ITEMS EI1 ,
1967: PA_PAY_EXP_TEMP T
1968: WHERE T.EXPENDITURE_ITEM_ID = EI1.EXPENDITURE_ITEM_ID
1969: AND EI1.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1970: AND EXISTS
1971: (SELECT 1
2076: fnd_global.user_id ,
2077: G_LOGIN_ID ,
2078: G_REQUEST_ID
2079: FROM
2080: PA_PAY_EXP_TEMP EII , -- change to PA_PAY_EXP_TEMP danger
2081: PA_PAY_SOURCE_AMOUNTS PSA
2082: WHERE
2083: PSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
2084: PSA.PRIORITY = Z.PRIORITY AND
2244: PAY_CURRENCY_CODE ,
2245: PAY_AMOUNT,
2246: SUM(QUANTITY) AS HOURS
2247: FROM
2248: PA_PAY_EXP_TEMP EI,
2249: PA_PAY_SOURCE_AMOUNTS PPSA
2250: WHERE
2251: PPSA.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
2252: PPSA.PRIORITY = Z.PRIORITY AND
2354: write_log(Log, ' Rejected Record count for Enable Misc processing ' || SQL%RowCount );
2355:
2356: /* Mark the EI as processed so that pay elements of a different priority / currency will not contribute to the same EI */
2357:
2358: UPDATE PA_PAY_EXP_TEMP
2359: SET PROCESSED_FLAG = 'Y'
2360: WHERE EXPENDITURE_ITEM_ID IN
2361: (SELECT DIST.SOURCE_EXPENDITURE_ITEM_ID
2362: FROM PA_PAY_DIST_LINES DIST,
2462: FROM ( SELECT NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id ,
2463: project_id ,
2464: task_id ,
2465: SUM(quantity) AS hours
2466: FROM PA_PAY_EXP_TEMP
2467: WHERE COALESCE(organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99) = COALESCE(X.PAY_SOURCE_ORGANIZATION_ID,organization_id ,OVERRIDE_TO_ORGANIZATION_ID,-99)
2468: AND project_id = Nvl(X.PAY_SOURCE_PROJECT_ID,project_id)
2469: AND task_id = Nvl(X.PAY_SOURCE_TASK_ID,task_id)
2470: --AND COST_DISTRIBUTED_FLAG <> 'Y'
2585: , NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id
2586: , project_id
2587: , task_id
2588: , SUM(NVL(quantity,0)) AS hours
2589: FROM PA_PAY_EXP_TEMP TEMP
2590: , PA_PAY_SOURCE_AMOUNTS S2
2591: WHERE S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2592: AND DISTRIBUTION_BASIS_CODE IN ('STHOURS'
2593: , 'OTHOURS'
2801: , NVL(organization_id ,OVERRIDE_TO_ORGANIZATION_ID) organization_id
2802: , project_id
2803: , task_id
2804: , SUM(acct_raw_cost) AS amount
2805: FROM PA_PAY_EXP_TEMP TEMP
2806: , PA_PAY_SOURCE_AMOUNTS S2
2807: WHERE S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
2808: AND DISTRIBUTION_BASIS_CODE IN ('ST_AMT'
2809: ,'OT_AMT'
3011: PROJECT_ID ,
3012: TASK_ID ,
3013: SUM(ACCT_RAW_COST) AS AMOUNT
3014: FROM
3015: PA_PAY_EXP_TEMP TEMP ,
3016: PA_PAY_SOURCE_AMOUNTS S2
3017: WHERE
3018: S2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
3019: DISTRIBUTION_BASIS_CODE = 'TOTAL_RAW' AND
4873: ORIG_CURR_CODE ,
4874: Sum(ORIG_AMOUNT) ORIG_AMOUNT ,
4875: SUM(DENOM_RAW_COST) DENOM_RAW_COST
4876: FROM
4877: PA_PAY_DIST_LINES DIST , PA_PAY_EXP_TEMP EI
4878: WHERE
4879: DIST.SOURCE_EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID AND
4880: DIST.SOURCE_EXPENDITURE_ITEM_ID IS NOT NULL AND
4881: DIST.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND
4951: DIST.SOURCE_EXPENDITURE_ITEM_ID,DIST.DENOM_CURRENCY_CODE,
4952: TEMP.COST_DISTRIBUTED_FLAG,TEMP.PAYROLL_ACCRUAL_FLAG , TEMP.ORIG_CURR_CODE,
4953: TEMP.ORIG_AMOUNT,TEMP.EXPENDITURE_ITEM_DATE
4954: FROM
4955: PA_PAY_DIST_LINES DIST , PA_PAY_EXP_TEMP TEMP
4956: WHERE
4957:
4958: DIST.SOURCE_EXPENDITURE_ITEM_ID IS NOT NULL AND
4959: DIST.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID AND