[Home] [Help]
1297: (
1298: SELECT
1299: EXP.INCURRED_BY_PERSON_ID
1300: FROM
1301: PA_EXPENDITURE_ITEMS ITEM,
1302: PA_EXPENDITURES EXP
1303: WHERE
1304: EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
1305: ITEM.SYSTEM_LINKAGE_FUNCTION IN ('OT', 'ST') AND
1364: (
1365: SELECT
1366: EXP.INCURRED_BY_PERSON_ID
1367: FROM
1368: PA_EXPENDITURE_ITEMS ITEM,
1369: PA_EXPENDITURES EXP
1370: WHERE
1371: EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
1372: EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
1429: (
1430: SELECT
1431: EXP.INCURRED_BY_PERSON_ID
1432: FROM
1433: PA_EXPENDITURE_ITEMS ITEM,
1434: PA_EXPENDITURES EXP
1435: WHERE
1436: EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
1437: EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
1493: (
1494: SELECT
1495: EXP.INCURRED_BY_PERSON_ID
1496: FROM
1497: PA_EXPENDITURE_ITEMS ITEM,
1498: PA_EXPENDITURES EXP
1499: WHERE
1500: EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID AND
1501: EXP.EXPENDITURE_STATUS_CODE = 'APPROVED' AND
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: */
1823: EII.QUANTITY,
1824: EII.INTERFACE_RUN_ID,
1825: EII.PAYROLL_ACCRUAL_FLAG,
1826: EII.TRANSFERRED_FROM_EXP_ITEM_ID
1827: FROM PA_EXPENDITURE_ITEMS EII,
1828: PA_EXPENDITURES EI
1829: WHERE EI.INCURRED_BY_PERSON_ID IN (SELECT DISTINCT PERSON_ID FROM PA_PAY_SOURCE_AMOUNTS
1830: WHERE
1831: INTERFACE_RUN_ID = G_INTERFACE_RUN_ID)
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
1870: -- AND NVL(temp.cost_distributed_flag,'N') = 'N'
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
1913: temp.expenditure_item_id= ei3.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
1913: temp.expenditure_item_id= ei3.expenditure_item_id
1914: AND ei3.transaction_source = 'ORACLE TIME AND LABOR'
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
1913: temp.expenditure_item_id= ei3.expenditure_item_id
1914: AND ei3.transaction_source = 'ORACLE TIME AND LABOR'
1915: AND ei2.orig_transaction_reference = ei3.orig_transaction_reference
1943: )
1944: =
1945: (SELECT EI3.ACCT_CURRENCY_CODE,
1946: EI3.ACCT_RAW_COST
1947: FROM PA_EXPENDITURE_ITEMS EI3
1948: WHERE EXPENDITURE_ITEM_ID =
1949: (SELECT MAX(EI5.EXPENDITURE_ITEM_ID)
1950: FROM PA_EXPENDITURE_ITEMS EI4 ,
1951: PA_EXPENDITURE_ITEMS EI5 ,
1946: EI3.ACCT_RAW_COST
1947: FROM PA_EXPENDITURE_ITEMS EI3
1948: WHERE EXPENDITURE_ITEM_ID =
1949: (SELECT MAX(EI5.EXPENDITURE_ITEM_ID)
1950: FROM PA_EXPENDITURE_ITEMS EI4 ,
1951: PA_EXPENDITURE_ITEMS EI5 ,
1952: PA_PAY_AUDIT AUD
1953: WHERE EI4.EXPENDITURE_ITEM_ID = T.EXPENDITURE_ITEM_ID
1954: AND EI4.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1947: FROM PA_EXPENDITURE_ITEMS EI3
1948: WHERE EXPENDITURE_ITEM_ID =
1949: (SELECT MAX(EI5.EXPENDITURE_ITEM_ID)
1950: FROM PA_EXPENDITURE_ITEMS EI4 ,
1951: PA_EXPENDITURE_ITEMS EI5 ,
1952: PA_PAY_AUDIT AUD
1953: WHERE EI4.EXPENDITURE_ITEM_ID = T.EXPENDITURE_ITEM_ID
1954: AND EI4.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1955: AND EI5.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1962: )
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
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
1972: FROM PA_EXPENDITURE_ITEMS EI2
1973: ,PA_PAY_AUDIT AUD2
1974: WHERE EI2.TRANSACTION_SOURCE = 'ORACLE TIME AND LABOR'
1975: AND EI2.INTERFACE_RUN_ID IS NOT NULL
1976: AND EI2.INTERFACE_RUN_ID = AUD2.INTERFACE_RUN_ID
3101: p_exp_item_date IN DATE)
3102: RETURN VARCHAR2 IS
3103:
3104:
3105: x_exp_id pa_expenditure_items_all.expenditure_id%type;
3106: x_exp_item_date pa_expenditure_items_all.expenditure_item_date%type;
3107:
3108:
3109:
3102: RETURN VARCHAR2 IS
3103:
3104:
3105: x_exp_id pa_expenditure_items_all.expenditure_id%type;
3106: x_exp_item_date pa_expenditure_items_all.expenditure_item_date%type;
3107:
3108:
3109:
3110: cursor get_emp_override_rule is
3123: ,detail.acct_rate_date_code
3124: ,detail.acct_exchange_rate
3125: ,detail.base_hours
3126: from pa_compensation_details detail
3127: -- ,pa_expenditure_items ei
3128: ,pa_expenditures exp
3129: where trunc(p_exp_item_date) between trunc(detail.start_date_active)
3130: and trunc(nvl(detail.end_date_active,p_exp_item_date))
3131: and detail.person_id = exp.incurred_by_person_id
3297: ,detail.acct_rate_date_code
3298: ,detail.acct_exchange_rate
3299: ,detail.base_hours
3300: from pa_compensation_details detail
3301: ,pa_expenditure_items ei
3302: ,pa_expenditures exp
3303: where trunc(p_exp_item_date) between trunc(detail.start_date_active)
3304: and trunc(nvl(detail.end_date_active,p_exp_item_date))
3305: and detail.person_id = exp.incurred_by_person_id
3890:
3891: PROCEDURE process_burden IS
3892: l_exp_group pa_expenditure_groups_all.expenditure_group%type;
3893: l_expenditure_id pa_expenditures_all.expenditure_id%type;
3894: l_exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
3895: i PLS_INTEGER;
3896: l_status VARCHAR2(100);
3897: l_msg_application VARCHAR2(10);
3898: l_msg_type VARCHAR2(1);
3901: l_msg_token3 VARCHAR2(2000);
3902: l_msg_count NUMBER;
3903: l_burden_exists VARCHAR2(1) := 'N';
3904: EI_PROCESS_FAILED EXCEPTION;
3905: l_billable_flag pa_expenditure_items_all.billable_flag%type;
3906: BEGIN
3907:
3908:
3909: BEGIN
4029: )
4030: LOOP
4031:
4032: i := i+1;
4033: select pa_expenditure_items_s.nextval
4034: into l_exp_item_id from dual;
4035:
4036:
4037: --Validate
4223: ); */
4224:
4225: INSERT
4226: INTO
4227: PA_EXPENDITURE_ITEMS_ALL
4228: (
4229: EXPENDITURE_ITEM_ID ,
4230: LAST_UPDATE_DATE ,
4231: LAST_UPDATED_BY ,
4325:
4326: END LOOP; -- End Y loop .. Exp Items
4327: -- pa_transactions.InsItems(1,0,'BTC','BTC',i, l_status,'N');
4328:
4329: /* UPDATE pa_expenditure_items_all
4330: SET interface_run_id = G_INTERFACE_RUN_ID
4331: ,COSTING_METHOD = 'ACTUAL'
4332: ,PAYROLL_ACCRUAL_FLAG = 'N'
4333: WHERE expenditure_id = l_expenditure_id ; */
4360:
4361: PROCEDURE process_misc IS
4362: l_exp_group pa_expenditure_groups_all.expenditure_group%type;
4363: l_expenditure_id pa_expenditures_all.expenditure_id%type;
4364: l_exp_item_id pa_expenditure_items_all.expenditure_item_id%type;
4365: i PLS_INTEGER;
4366: l_status VARCHAR2(100);
4367: l_msg_application VARCHAR2(10);
4368: l_msg_type VARCHAR2(1);
4371: l_msg_token3 VARCHAR2(2000);
4372: l_msg_count NUMBER;
4373: l_misc_exists VARCHAR2(1) := 'N';
4374: EI_PROCESS_FAILED EXCEPTION;
4375: l_billable_flag pa_expenditure_items_all.billable_flag%TYPE;
4376:
4377:
4378:
4379: BEGIN
4513: )
4514: LOOP
4515:
4516: i := i+1;
4517: select pa_expenditure_items_s.nextval
4518: into l_exp_item_id from dual;
4519:
4520:
4521:
4709: */
4710:
4711: INSERT
4712: INTO
4713: PA_EXPENDITURE_ITEMS_ALL
4714: (
4715: EXPENDITURE_ITEM_ID ,
4716: LAST_UPDATE_DATE ,
4717: LAST_UPDATED_BY ,
4817:
4818: END LOOP; -- End Y loop .. Exp Items
4819: /* -- pa_transactions.InsItems(1,0,'PJ','PJ',i, l_status,'N');
4820:
4821: UPDATE pa_expenditure_items_all
4822: SET interface_run_id = G_INTERFACE_RUN_ID
4823: ,COSTING_METHOD = 'ACTUAL'
4824: ,PAYROLL_ACCRUAL_FLAG = 'N'
4825: WHERE expenditure_id = l_expenditure_id ;
4856: --paid_ei_for_update Eid_TabType;
4857: l_transfer_id NUMBER(15);
4858: l_reversal_id NUMBER(15);
4859: l_item_comment pa_expenditure_comments.expenditure_comment%TYPE;
4860: l_denom_raw_cost pa_expenditure_items_all.denom_raw_cost%TYPE;
4861: l_denom_orig_amount pa_expenditure_items_all.denom_raw_cost%TYPE;
4862:
4863: EI_PROCESS_FAILED EXCEPTION;
4864:
4857: l_transfer_id NUMBER(15);
4858: l_reversal_id NUMBER(15);
4859: l_item_comment pa_expenditure_comments.expenditure_comment%TYPE;
4860: l_denom_raw_cost pa_expenditure_items_all.denom_raw_cost%TYPE;
4861: l_denom_orig_amount pa_expenditure_items_all.denom_raw_cost%TYPE;
4862:
4863: EI_PROCESS_FAILED EXCEPTION;
4864:
4865:
4925: write_log(LOG,'l_denom_raw_cost' || l_denom_raw_cost) ;
4926:
4927:
4928: UPDATE
4929: PA_EXPENDITURE_ITEMS_ALL EI
4930: SET DENOM_CURRENCY_CODE = X.DENOM_CURRENCY_CODE
4931: ,DENOM_RAW_COST = PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(l_denom_raw_cost, X.DENOM_CURRENCY_CODE)
4932: ,REQUEST_ID = G_REQUEST_ID
4933: ,INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
4964:
4965: -- Accrual EI , but was marked for re-calc.
4966: -- Mark them back as costed and copy amounts from CDL
4967: IF ((X.COST_DISTRIBUTED_FLAG = 'N') AND (X.PAYROLL_ACCRUAL_FLAG = 'Y')) THEN
4968: UPDATE PA_EXPENDITURE_ITEMS EI
4969: SET
4970: EI.COST_DISTRIBUTED_FLAG='Y',
4971: (EI.RAW_COST , EI.RAW_COST_RATE, EI.BURDEN_COST, EI.BURDEN_COST_RATE,
4972: EI.ACCT_RAW_COST, EI.ACCT_BURDENED_COST, EI.DENOM_RAW_COST,
5034: -- Find the reversal expenditure item and create CDL for that .
5035: BEGIN
5036:
5037: SELECT ei.expenditure_item_id INTO l_reversal_id
5038: FROM pa_expenditure_items ei
5039: WHERE ei.adjusted_expenditure_item_id= X.SOURCE_EXPENDITURE_ITEM_ID
5040: AND ei.net_zero_adjustment_flag = 'Y'
5041: AND ei.cost_distributed_flag='N';
5042:
5081:
5082:
5083: INSERT
5084: INTO
5085: PA_EXPENDITURE_ITEMS_ALL
5086: (
5087: EXPENDITURE_ITEM_ID ,
5088: LAST_UPDATE_DATE ,
5089: LAST_UPDATED_BY ,
5493: /* RATE_SOURCE_CODE , */
5494: 'N',
5495: G_INTERFACE_RUN_ID
5496: FROM
5497: PA_EXPENDITURE_ITEMS_ALL ei,
5498: ( SELECT source_expenditure_item_id seid ,denom_CURRENCY_CODE,Sum(denom_raw_cost) denom_raw_cost, Sum(denom_raw_cost) acct_raw_cost FROM
5499: pa_pay_dist_lines
5500: WHERE
5501: source_expenditure_item_id IS NOT NULL
5554: G_PROG_APPL_ID ,
5555: G_RUN_DATE );
5556: write_log(LOG, 'Audit record Inserted');
5557:
5558: UPDATE pa_expenditure_items
5559: SET interface_run_id = G_INTERFACE_RUN_ID
5560: ,COSTING_METHOD = 'ACTUAL'
5561: ,PAYROLL_ACCRUAL_FLAG = 'N'
5562: WHERE expenditure_id = l_transfer_id ;
5564: END LOOP;
5565:
5566: /*
5567:
5568: UPDATE PA_EXPENDITURE_ITEMS_ALL EI2
5569: SET EI2.DENOM_RAW_COST = 0 ,
5570: EI2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5571: WHERE EI2.EXPENDITURE_ITEM_ID IN
5572: ( SELECT EI.EXPENDITURE_ITEM_ID
5569: SET EI2.DENOM_RAW_COST = 0 ,
5570: EI2.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5571: WHERE EI2.EXPENDITURE_ITEM_ID IN
5572: ( SELECT EI.EXPENDITURE_ITEM_ID
5573: FROM PA_EXPENDITURE_ITEMS_ALL EI ,
5574: PA_EXPENDITURES_ALL E,
5575: PA_PAY_AUDIT AUD
5576: WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
5577: AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5588: /* Start changes for bug#12975007*/
5589:
5590: /* Update all netzero OTL EI's between pay start and dates with zero payroll cost */
5591:
5592: UPDATE PA_EXPENDITURE_ITEMS Ei2 SET
5593: (EI2.DENOM_RAW_COST,Ei2.INTERFACE_RUN_ID,Ei2.
5594: COSTING_METHOD,Ei2.PAYROLL_ACCRUAL_FLAG,Ei2.REQUEST_ID) = (SELECT
5595: NVL((-1*EI.DENOM_RAW_COST),0),
5596: G_INTERFACE_RUN_ID,'ACTUAL',NVL(EI.PAYROLL_ACCRUAL_FLAG,'N'),G_REQUEST_ID FROM
5593: (EI2.DENOM_RAW_COST,Ei2.INTERFACE_RUN_ID,Ei2.
5594: COSTING_METHOD,Ei2.PAYROLL_ACCRUAL_FLAG,Ei2.REQUEST_ID) = (SELECT
5595: NVL((-1*EI.DENOM_RAW_COST),0),
5596: G_INTERFACE_RUN_ID,'ACTUAL',NVL(EI.PAYROLL_ACCRUAL_FLAG,'N'),G_REQUEST_ID FROM
5597: PA_EXPENDITURE_ITEMS Ei,
5598: PA_EXPENDITURES E,
5599: PA_PAY_AUDIT AUD
5600: WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
5601: AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_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
5611: WHERE EI3.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5612: AND SUBSTR(EI3.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI3.ORIG_TRANSACTION_REFERENCE,':')-1) =
5613: SUBSTR(EI.ORIG_TRANSACTION_REFERENCE,1,INSTR(EI.ORIG_TRANSACTION_REFERENCE,':')-1)
5614: ))
5623:
5624: /* Start changes for bug#12975007*/
5625:
5626: FOR X IN (SELECT EI2.EXPENDITURE_ITEM_ID,EI2.TRANSACTION_SOURCE,EI2.ADJUSTED_EXPENDITURE_ITEM_ID
5627: FROM PA_EXPENDITURE_ITEMS EI2 ,
5628: PA_EXPENDITURES E,
5629: PA_PAY_AUDIT AUD
5630: WHERE E.INCURRED_BY_PERSON_ID = G_PERSON_ID
5631: AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
5639: )
5640:
5641: LOOP
5642: --{
5643: UPDATE PA_EXPENDITURE_ITEMS EI2
5644: SET (EI2.DENOM_RAW_COST,EI2.INTERFACE_RUN_ID,
5645: EI2.COSTING_METHOD,
5646: EI2.PAYROLL_ACCRUAL_FLAG,
5647: EI2.REQUEST_ID) = (SELECT
5644: SET (EI2.DENOM_RAW_COST,EI2.INTERFACE_RUN_ID,
5645: EI2.COSTING_METHOD,
5646: EI2.PAYROLL_ACCRUAL_FLAG,
5647: EI2.REQUEST_ID) = (SELECT
5648: /*(NVL ( ( SELECT (-1*EI1.DENOM_RAW_COST) FROM PA_EXPENDITURE_ITEMS EI1 WHERE
5649: EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),0 ) ),G_INTERFACE_RUN_ID,'ACTUAL',
5650: (NVL ( ( SELECT EI1.PAYROLL_ACCRUAL_FLAG FROM
5651: PA_EXPENDITURE_ITEMS EI1 WHERE
5652: EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),'N' )),
5647: EI2.REQUEST_ID) = (SELECT
5648: /*(NVL ( ( SELECT (-1*EI1.DENOM_RAW_COST) FROM PA_EXPENDITURE_ITEMS EI1 WHERE
5649: EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),0 ) ),G_INTERFACE_RUN_ID,'ACTUAL',
5650: (NVL ( ( SELECT EI1.PAYROLL_ACCRUAL_FLAG FROM
5651: PA_EXPENDITURE_ITEMS EI1 WHERE
5652: EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),'N' )),
5653: --'N',
5654: G_REQUEST_ID FROM DUAL)*/
5655: NVL((-1*EI1.DENOM_RAW_COST),0),G_INTERFACE_RUN_ID,'ACTUAL',NVL(EI1.PAYROLL_ACCRUAL_FLAG,'N'),G_REQUEST_ID
5652: EI1.EXPENDITURE_ITEM_ID=EI2.ADJUSTED_EXPENDITURE_ITEM_ID),'N' )),
5653: --'N',
5654: G_REQUEST_ID FROM DUAL)*/
5655: NVL((-1*EI1.DENOM_RAW_COST),0),G_INTERFACE_RUN_ID,'ACTUAL',NVL(EI1.PAYROLL_ACCRUAL_FLAG,'N'),G_REQUEST_ID
5656: FROM PA_EXPENDITURE_ITEMS EI1
5657: WHERE
5658: EI1.EXPENDITURE_ITEM_ID=NVL(EI2.ADJUSTED_EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_ID))
5659: WHERE EI2.EXPENDITURE_ITEM_ID IN
5660: (SELECT EXPENDITURE_ITEM_ID
5657: WHERE
5658: EI1.EXPENDITURE_ITEM_ID=NVL(EI2.ADJUSTED_EXPENDITURE_ITEM_ID,EI2.EXPENDITURE_ITEM_ID))
5659: WHERE EI2.EXPENDITURE_ITEM_ID IN
5660: (SELECT EXPENDITURE_ITEM_ID
5661: FROM PA_EXPENDITURE_ITEMS Ei3
5662: ,PA_EXPENDITURES E,
5663: PA_PAY_AUDIT AUD
5664: WHERE EI3.EXPENDITURE_ITEM_ID IN
5665: (SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
5661: FROM PA_EXPENDITURE_ITEMS Ei3
5662: ,PA_EXPENDITURES E,
5663: PA_PAY_AUDIT AUD
5664: WHERE EI3.EXPENDITURE_ITEM_ID IN
5665: (SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
5666: CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID=EXPENDITURE_ITEM_ID
5667: UNION
5668: SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
5669: CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID=ADJUSTED_EXPENDITURE_ITEM_ID
5664: WHERE EI3.EXPENDITURE_ITEM_ID IN
5665: (SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
5666: CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID=EXPENDITURE_ITEM_ID
5667: UNION
5668: SELECT EXPENDITURE_ITEM_ID FROM PA_EXPENDITURE_ITEMS START WITH EXPENDITURE_ITEM_ID= X.EXPENDITURE_ITEM_ID
5669: CONNECT BY PRIOR TRANSFERRED_FROM_EXP_ITEM_ID=ADJUSTED_EXPENDITURE_ITEM_ID
5670: )
5671: AND E.INCURRED_BY_PERSON_ID = G_PERSON_ID
5672: AND AUD.INTERFACE_RUN_ID = G_INTERFACE_RUN_ID
6030:
6031:
6032: /* ========================================================================
6033: PROCEDURE CreateReverseCdl:Creates the reversal cdl for backout items
6034: and mark the CDF on pa_expenditure_items_all
6035: ========================================================================*/
6036:
6037: PROCEDURE CreateReverseCdl ( X_exp_item_id IN NUMBER,
6038: X_backout_id IN NUMBER,
6076: p_project_rate_type pa_cost_distribution_lines.project_rate_type%TYPE;
6077: p_project_exchange_rate pa_cost_distribution_lines.project_exchange_rate%TYPE;
6078: p_project_id pa_cost_distribution_lines.project_id%TYPE;
6079: p_task_id pa_cost_distribution_lines.task_id%TYPE;
6080: p_parent_adjusted_id pa_expenditure_items.adjusted_expenditure_item_id%TYPE;
6081: p_parent_transferred_id pa_expenditure_items.transferred_from_exp_item_id%TYPE;
6082: p_gl_accounted_flag pa_transaction_sources.gl_accounted_flag%TYPE;
6083: p_transaction_source pa_transaction_sources.transaction_source%TYPE;
6084: l_si_assets_addition_flag pa_cost_distribution_lines.si_assets_addition_flag%TYPE ;
6077: p_project_exchange_rate pa_cost_distribution_lines.project_exchange_rate%TYPE;
6078: p_project_id pa_cost_distribution_lines.project_id%TYPE;
6079: p_task_id pa_cost_distribution_lines.task_id%TYPE;
6080: p_parent_adjusted_id pa_expenditure_items.adjusted_expenditure_item_id%TYPE;
6081: p_parent_transferred_id pa_expenditure_items.transferred_from_exp_item_id%TYPE;
6082: p_gl_accounted_flag pa_transaction_sources.gl_accounted_flag%TYPE;
6083: p_transaction_source pa_transaction_sources.transaction_source%TYPE;
6084: l_si_assets_addition_flag pa_cost_distribution_lines.si_assets_addition_flag%TYPE ;
6085: p_err_code NUMBER;
6108:
6109: l_pa_date DATE ;
6110: l_recvr_pa_date DATE ;
6111: l_ei_date DATE ;
6112: l_org_id pa_expenditure_items.org_id%type;
6113:
6114: actual_cdl_line_num pa_cost_distribution_lines.line_num%TYPE;
6115:
6116:
6113:
6114: actual_cdl_line_num pa_cost_distribution_lines.line_num%TYPE;
6115:
6116:
6117: l_recvr_org_id pa_expenditure_items.org_id%type;
6118: l_gl_date DATE;
6119: l_recvr_gl_date DATE;
6120: l_exp_id NUMBER;
6121: l_sob_id NUMBER;
6147: ,l_recvr_org_id
6148: ,l_sys_link_function
6149: ,l_exp_id
6150:
6151: FROM pa_expenditure_items_All ITEMS,
6152: pa_transaction_sources TRN
6153: WHERE ITEMS.transaction_source = TRN.transaction_source (+)
6154: AND ITEMS.expenditure_item_id = X_exp_item_id;
6155:
6372: /* 12.2 payroll intg .. end */
6373: IF p_err_code IS NOT NULL THEN
6374: raise e_cdl_error;
6375: END IF;
6376: UPDATE pa_expenditure_items
6377: SET cost_distributed_flag = 'Y'
6378: WHERE expenditure_item_id = X_backout_id;
6379: END IF;
6380: X_status := 0;