[Home] [Help]
895: AND ei.transaction_source in ('AP INVOICE','AP VARIANCE','AP NRTAX','AP EXPENSE')) ;
896:
897:
898: /*Bug 13602288: Self Assessed Tax Changes - Marking the SAT Lines also to N in the cleanup*/
899: UPDATE ap_self_assessed_tax_dist_all DIST
900: SET DIST.pa_addition_flag = 'N'
901: , DIST.request_id = G_REQUEST_ID
902: WHERE DIST.pa_addition_flag IN ('O','W')
903: AND DIST.posted_flag||''= 'Y'
1398: end if;
1399:
1400: /*Bug 13602288: Self Assessed Tax Changes - NetZero Adjustments need to be handled for Expense Reprort SAT Lines as well*/
1401:
1402: UPDATE ap_self_assessed_tax_dist dist
1403: SET dist.pa_addition_flag = 'Z',
1404: request_id = G_REQUEST_ID,
1405: last_update_date = SYSDATE,
1406: last_updated_by = G_USER_ID,
1418: AND inv.invoice_type_lookup_code = 'EXPENSE REPORT')
1419: AND ( -- ( nvl(dist.encumbered_flag,'N') = 'R' ) OR /* Commented for bug#13940879 */
1420: (dist.line_type_lookup_code ='NONREC_TAX' AND nvl(dist.base_amount,dist.amount)=0))
1421: AND NOT EXISTS (SELECT NULL
1422: FROM ap_self_assessed_tax_dist dist1
1423: WHERE dist.parent_reversal_id is not null
1424: AND dist.parent_reversal_id = dist1.invoice_distribution_id
1425: AND dist1.pa_addition_flag = 'T'
1426: AND dist1.encumbered_flag = 'R'
1500:
1501:
1502: /*Bug 13602288: Self Assessed Tax Changes - Netzero Adjustment to be marked when the */
1503:
1504: UPDATE ap_self_assessed_tax_dist dist
1505: SET dist.pa_addition_flag = 'Z',
1506: request_id = G_REQUEST_ID,
1507: last_update_date = SYSDATE,
1508: last_updated_by = G_USER_ID,
1526: AND inv.invoice_type_lookup_code = 'EXPENSE REPORT')
1527: AND ( -- ( nvl(dist.encumbered_flag,'N') = 'R' ) OR /* Commented for bug#13940879 */
1528: (dist.line_type_lookup_code ='NONREC_TAX' AND nvl(dist.base_amount,dist.amount)=0))
1529: AND NOT EXISTS (SELECT NULL
1530: FROM ap_self_assessed_tax_dist dist1
1531: WHERE dist.parent_reversal_id is not null
1532: AND dist.parent_reversal_id = dist1.invoice_distribution_id
1533: AND dist1.pa_addition_flag = 'T'
1534: AND dist1.encumbered_flag = 'R'
1619: End If;
1620:
1621:
1622: /*Bug 13602288: Self Assessed Tax Changes*/
1623: UPDATE AP_SELF_ASSESSED_TAX_DIST dist
1624: SET dist.pa_addition_flag = 'Z',
1625: request_id = G_REQUEST_ID,
1626: last_update_date=SYSDATE,
1627: last_updated_by= G_USER_ID,
1636: AND dist.project_id = G_PROJECT_ID
1637: AND ( -- ( nvl(dist.encumbered_flag,'N') = 'R' ) OR /* Commented for bug#13940879 */
1638: (dist.line_type_lookup_code ='NONREC_TAX' AND nvl(dist.base_amount,dist.amount)=0))
1639: AND NOT EXISTS (SELECT NULL
1640: FROM AP_SELF_ASSESSED_TAX_DIST dist1
1641: WHERE dist.parent_reversal_id is not null
1642: AND dist.parent_reversal_id = dist1.invoice_distribution_id
1643: AND dist1.pa_addition_flag = 'T'
1644: AND dist1.encumbered_flag = 'R'
1717: write_log(LOG, G_err_stage);
1718: End If;
1719:
1720: /*Bug 13602288: Self Assessed Tax Changes*/
1721: UPDATE AP_SELF_ASSESSED_TAX_DIST dist
1722: SET dist.pa_addition_flag = 'Z',
1723: request_id = G_REQUEST_ID,
1724: last_update_date = SYSDATE,
1725: last_updated_by = G_USER_ID,
1739: AND dist.project_id > 0
1740: AND ( -- ( nvl(dist.encumbered_flag,'N') = 'R' ) OR /* Commented for bug#13940879 */
1741: (dist.line_type_lookup_code ='NONREC_TAX' AND nvl(dist.base_amount,dist.amount)=0))
1742: AND NOT EXISTS (SELECT NULL
1743: FROM AP_SELF_ASSESSED_TAX_DIST dist1
1744: WHERE dist.parent_reversal_id is not null
1745: AND dist.parent_reversal_id = dist1.invoice_distribution_id
1746: AND dist1.pa_addition_flag = 'T'
1747: AND dist1.encumbered_flag = 'R'
1793: WHERE invoice_distribution_id = p_prepay_dist_id;
1794: else
1795: SELECT prepay_amount_remaining
1796: INTO l_prepay_rem_amt
1797: FROM ap_self_assessed_tax_dist_All
1798: WHERE invoice_distribution_id = p_prepay_dist_id;
1799: end if;
1800: -- ==================================================================================
1801: -- Bug: 5393523
1937: write_log(LOG, 'Number of Historical rows marked to O: ' || to_char(SQL%ROWCOUNT));
1938:
1939: --code changes for bug 14769043
1940: /*Bug 13602288: Self Assessed Tax Changes*/
1941: UPDATE ap_self_assessed_tax_dist DIST
1942: SET DIST.Pa_Addition_Flag ='O',
1943: request_id = G_REQUEST_ID,
1944: last_update_date=SYSDATE,
1945: last_updated_by=G_USER_ID,
1954: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
1955: AND ((
1956: exists (SELECT NULL
1957: FROM AP_INVOICES inv,
1958: ap_self_assessed_tax_dist DIST1,
1959: PO_VENDORS vend
1960: WHERE inv.invoice_id = DIST1.invoice_id
1961: AND DIST1.invoice_distribution_id = DIST.invoice_distribution_id
1962: AND INV.payment_status_flag = 'Y' -- Flag indicated FULLY paid inv
1981: OR
1982: (EXISTS ( SELECT NULL
1983: FROM PO_VENDORS vend1,
1984: ap_invoices inv1,
1985: ap_self_assessed_tax_dist dist2
1986: WHERE inv1.invoice_id = dist2.invoice_id
1987: AND inv1.invoice_id = dist.invoice_id
1988: AND (dist.reversal_flag = 'Y' or dist.cancellation_flag = 'Y' )
1989: AND dist2.invoice_distribution_id = dist.parent_reversal_id --Process Historical data reversals as Invoices in Cash based
2072: G_DISTRIBUTIONS_MARKED := nvl(G_DISTRIBUTIONS_MARKED,0) + SQL%ROWCOUNT;
2073: write_log(LOG, 'Number of Historical PREPAY Appl Dist marked to O: ' || to_char(SQL%ROWCOUNT));
2074:
2075: /*Bug 13602288: Self Assessed Tax Changes*/
2076: UPDATE ap_self_assessed_tax_dist_all dist
2077: SET dist.pa_addition_flag = 'O',
2078: request_id = G_REQUEST_ID,
2079: last_update_date=SYSDATE,
2080: last_updated_by= G_USER_ID,
2112: and PTYPE.PERSON_TYPE_ID = PPL.PERSON_TYPE_ID
2113: and PTYPE.SYSTEM_PERSON_TYPE in ('OTHER','CWK')))))
2114: AND exists(SELECT inv.invoice_id
2115: FROM AP_INVOICES inv,
2116: ap_self_assessed_tax_dist_all aid
2117: WHERE aid.invoice_id = inv.invoice_id
2118: AND inv.invoice_type_lookup_code = 'PREPAYMENT'
2119: --aND aid.historical_flag = 'Y'
2120: AND aid.pa_addition_flag = 'Y'
2172:
2173: /*Bug 13602288: Self Assessed Tax Changes*/
2174: write_log(LOG, 'Marking Expense Report type invoices sat lines for processing - Accrual Acct');
2175:
2176: UPDATE ap_self_assessed_tax_dist DIST
2177: SET DIST.Pa_Addition_Flag ='O',
2178: request_id = G_REQUEST_ID,
2179: last_update_date=SYSDATE,
2180: last_updated_by=G_USER_ID,
2297:
2298: /*Bug 13602288: Self Assessed Tax Changes*/
2299: write_log(LOG, 'Marking invoices to O for SAT Data interface in Cash Based Acctng');
2300:
2301: UPDATE ap_self_assessed_tax_dist DIST
2302: SET DIST.Pa_Addition_Flag ='O',
2303: request_id = G_REQUEST_ID,
2304: last_update_date=SYSDATE,
2305: last_updated_by=G_USER_ID,
2314: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
2315: AND (
2316: (exists (SELECT NULL
2317: FROM AP_INVOICES inv,
2318: AP_self_assessed_tax_dist DIST1,
2319: PO_VENDORS vend
2320: WHERE inv.invoice_id = DIST1.invoice_id
2321: AND DIST1.invoice_distribution_id = DIST.invoice_distribution_id
2322: AND INV.payment_status_flag = 'Y' -- Flag indicated FULLY paid inv
2428: G_DISTRIBUTIONS_MARKED := nvl(G_DISTRIBUTIONS_MARKED,0) + SQL%ROWCOUNT;
2429: write_log(LOG, 'Number of Historical PREPAY Appl Dist marked to O: ' || to_char(SQL%ROWCOUNT));
2430:
2431: /*Bug 13602288: Self Assessed Tax Changes*/
2432: UPDATE ap_self_assessed_tax_dist dist
2433: SET dist.pa_addition_flag = 'O',
2434: request_id = G_REQUEST_ID,
2435: last_update_date=SYSDATE,
2436: last_updated_by= G_USER_ID,
2467: and PTYPE.SYSTEM_PERSON_TYPE in ('OTHER','CWK')))
2468: ))
2469: AND exists(SELECT inv.invoice_id
2470: FROM AP_INVOICES_ALL inv,
2471: ap_self_assessed_tax_dist_all aid
2472: WHERE aid.invoice_id = inv.invoice_id
2473: AND inv.invoice_type_lookup_code = 'PREPAYMENT'
2474: --AND aid.historical_flag = 'Y'
2475: AND aid.pa_addition_flag = 'Y'
2524: G_DISTRIBUTIONS_MARKED := SQL%ROWCOUNT;
2525: write_log(LOG, 'Number of rows marked to O: ' || to_char(SQL%ROWCOUNT));
2526:
2527: /*Bug 13602288: Self Assessed Tax Changes*/
2528: UPDATE ap_self_assessed_tax_dist DIST
2529: SET DIST.Pa_Addition_Flag ='O',
2530: request_id = G_REQUEST_ID,
2531: last_update_date=SYSDATE,
2532: last_updated_by=G_USER_ID,
2788: Pre Rel12 Upgrade should also be brought into Projects */
2789:
2790: write_log(LOG, 'Marking PREPAYMENT Self Assessed Tax lines in Cash Based Acctng');
2791:
2792: UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL dist
2793: SET dist.pa_addition_flag = 'O',
2794: request_id = G_REQUEST_ID,
2795: last_update_date=SYSDATE,
2796: last_updated_by= G_USER_ID,
2816: AND nvl(INV.source, 'xx' ) NOT IN (
2817: 'PA_IC_INVOICES','PA_COST_ADJUSTMENTS') )
2818: AND exists(SELECT inv.invoice_id
2819: FROM AP_INVOICES inv,
2820: AP_SELF_ASSESSED_TAX_DIST_ALL aid
2821: WHERE aid.invoice_id = inv.invoice_id
2822: AND inv.invoice_type_lookup_code = 'PREPAYMENT'
2823: -- AND aid.historical_flag = 'Y' /*doubt*/
2824: AND aid.pa_addition_flag = 'Y'
2840: )
2841: )
2842: AND NOT EXISTS (SELECT 'X'
2843: FROM
2844: AP_SELF_ASSESSED_TAX_DIST_ALL apdist,
2845: po_distributions pod,
2846: mtl_system_items si,
2847: po_lines_all pol
2848: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
2950: write_log(LOG, 'Number of rows marked to O: ' || to_char(SQL%ROWCOUNT));
2951:
2952:
2953: /*Bug 13602288: Self Assessed Tax Changes */
2954: UPDATE AP_SELF_ASSESSED_TAX_DIST DIST
2955: SET DIST.Pa_Addition_Flag = 'O',
2956: request_id = G_REQUEST_ID,
2957: last_update_date=SYSDATE,
2958: last_updated_by=G_USER_ID,
2972: AND EXISTS (
2973: SELECT NULL
2974: FROM ap_invoices_all inv,
2975: po_distributions_all PO,
2976: AP_SELF_ASSESSED_TAX_DIST dist2
2977: WHERE inv.invoice_id = dist2.invoice_id
2978: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
2979: AND dist2.invoice_id = DIST.invoice_id
2980: /*credit card txn enhancement, make sure this update doesn't pick tehm */
3006: )
3007: )
3008: AND NOT EXISTS (SELECT 'X'
3009: FROM
3010: AP_SELF_ASSESSED_TAX_DIST apdist,
3011: po_distributions pod,
3012: mtl_system_items si,
3013: po_lines_all pol
3014: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
3230: Pre Rel12 Upgrade should also be brought into Projects */
3231:
3232: write_log(LOG, 'Marking Historical Self Assessed Tax Prepayments for processing...');
3233:
3234: UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL dist
3235: SET dist.pa_addition_flag = 'O',
3236: request_id = G_REQUEST_ID,
3237: last_update_date=SYSDATE,
3238: last_updated_by= G_USER_ID,
3257: AND nvl(INV.source, 'xx' ) NOT IN (
3258: 'PA_IC_INVOICES','PA_COST_ADJUSTMENTS') )
3259: AND exists(SELECT inv.invoice_id
3260: FROM AP_INVOICES_all inv,
3261: AP_SELF_ASSESSED_TAX_DIST_ALL aid
3262: WHERE aid.invoice_id = inv.invoice_id
3263: AND inv.invoice_type_lookup_code = 'PREPAYMENT'
3264: -- AND aid.historical_flag = 'Y' /*doubt*/
3265: AND aid.pa_addition_flag = 'Y'
3281: )
3282: )
3283: AND NOT EXISTS (SELECT 'X'
3284: FROM
3285: AP_SELF_ASSESSED_TAX_DIST_ALL apdist,
3286: po_distributions pod,
3287: mtl_system_items si,
3288: po_lines_all pol
3289: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
3395: write_log(LOG, 'Number of rows marked to O: ' || to_char(SQL%ROWCOUNT));
3396:
3397: /*Bug 13602288: Self Assessed Tax Changes*/
3398:
3399: UPDATE AP_SELF_ASSESSED_TAX_DIST DIST
3400: SET DIST.Pa_Addition_Flag = 'O',
3401: request_id = G_REQUEST_ID,
3402: last_update_date=SYSDATE,
3403: last_updated_by=G_USER_ID,
3416: AND EXISTS (
3417: SELECT NULL
3418: FROM ap_invoices_all inv,
3419: po_distributions_all PO,
3420: AP_SELF_ASSESSED_TAX_DIST dist2
3421: WHERE inv.invoice_id = dist2.invoice_id
3422: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
3423: AND dist2.invoice_id = DIST.invoice_id
3424: AND inv.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
3449: )
3450: )
3451: AND NOT EXISTS (SELECT 'X'
3452: FROM
3453: AP_SELF_ASSESSED_TAX_DIST apdist,
3454: po_distributions pod,
3455: mtl_system_items si,
3456: po_lines_all pol
3457: where DIST.CHARGE_APPLICABLE_TO_DIST_ID
3551: WHERE invoice_id = p_invoice_id
3552: AND pa_addition_flag = 'O'
3553: union
3554: select 1
3555: from AP_SELF_ASSESSED_TAX_DIST
3556: WHERE invoice_id = p_invoice_id
3557: AND pa_addition_flag = 'O'
3558: ); /*Bug 13602288: Self Assessed Tax Changes*/
3559:
3674: AND l_is_self_assessed_tax_tbl(i) = 'N' -- Bug 13602288: Self Assessed Tax Changes
3675: AND l_insert_flag_tbl(i) in ('P','F');
3676:
3677: FORALL i IN l_invoice_id_tbl.FIRST..l_invoice_id_tbl.LAST
3678: UPDATE AP_SELF_ASSESSED_TAX_DIST dist
3679: SET dist.pa_addition_flag = decode(l_insert_flag_tbl(i),'F','G','P','N')
3680: WHERE dist.invoice_id = l_invoice_id_tbl(i)
3681: AND dist.invoice_distribution_id = l_invoice_dist_id_tbl(i)
3682: AND dist.pa_addition_flag = 'O'
5293:
5294: /*Bug 13602288: Self Assessed Tax Changes*/
5295: FORALL i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST
5296:
5297: UPDATE ap_self_assessed_tax_dist_all dist
5298: SET dist.pa_addition_flag = l_pa_addflag_tbl(i)
5299: ,dist.assets_addition_flag = decode(l_assets_addflag_tbl(i),'P','P',dist.assets_addition_flag)
5300: WHERE dist.invoice_id = l_sys_ref2_tbl(i)
5301: AND dist.invoice_distribution_id = l_sys_ref5_tbl(i)
9044: write_log(LOG,'Number of inv amount variance lines marked to G: '||to_char(SQL%ROWCOUNT));
9045: write_log(LOG,'Number of inv amount variance lines marked to W: '||G_NUM_AP_VARIANCE_MARKED_W);
9046:
9047: /*Commented out for bBug 13602288: Self Assessed Tax Changes
9048: UPDATE ap_self_assessed_tax_dist_all apdist
9049: SET apdist.pa_addition_flag = DECODE(NVL(apdist.amount_variance,0),0,'G',increment_W_count('W')),
9050: request_id = G_REQUEST_ID,
9051: last_update_date=SYSDATE,
9052: last_updated_by=G_USER_ID,
9055: program_application_id= G_PROG_APPL_ID,
9056: program_update_date=SYSDATE
9057: WHERE rowid IN (
9058: SELECT dist.rowid
9059: FROM ap_invoices inv, ap_self_assessed_tax_dist_all dist
9060: WHERE inv.invoice_id = dist.invoice_id
9061: AND (dist.line_type_lookup_code in ('ITEM','ACCRUAL','RETROACCRUAL','NONREC_TAX')
9062: OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
9063: AND EXISTS (SELECT 'X'
9072: AND si.organization_id = po_dist1.org_id)
9073: )
9074: ) --Bug#5399352. Added this here to take care of IPV/TIPV records matched to EIB item PO.
9075: AND NOT EXISTS (SELECT NULL
9076: FROM ap_self_assessed_tax_dist_all apdist2
9077: WHERE apdist2.pa_addition_flag = 'Y'
9078: AND apdist2.po_distribution_id = dist.po_distribution_id
9079: AND apdist2.line_type_lookup_code = dist.line_type_lookup_code
9080: AND apdist2.line_type_lookup_code in ('ITEM','ACCRUAL','RETROACCRUAL','NONREC_TAX')
9079: AND apdist2.line_type_lookup_code = dist.line_type_lookup_code
9080: AND apdist2.line_type_lookup_code in ('ITEM','ACCRUAL','RETROACCRUAL','NONREC_TAX')
9081: )
9082: AND ( EXISTS (SELECT NULL
9083: FROM ap_self_assessed_tax_dist_all apdist1,
9084: ap_invoices_all ap1 /*Changes for bug 7650946 -- Start
9085: WHERE apdist1.pa_addition_flag IN ('F', 'G')
9086: AND ap1.invoice_id = apdist1.invoice_id /*Added for bug 7650946
9087: AND apdist1.po_distribution_id = dist.po_distribution_id
9266: write_log(LOG,'Number of inv amount variance lines marked to W: '||G_NUM_AP_VARIANCE_MARKED_W);
9267:
9268:
9269: /*Commented for Bug 13602288: Self Assessed Tax Changes
9270: UPDATE ap_self_assessed_tax_dist_all apdist
9271: SET apdist.pa_addition_flag = DECODE(NVL(apdist.amount_variance,0),0,'G',increment_W_count('W')),
9272: request_id = G_REQUEST_ID,
9273: last_update_date=SYSDATE,
9274: last_updated_by=G_USER_ID,
9277: program_application_id= G_PROG_APPL_ID,
9278: program_update_date=SYSDATE
9279: WHERE rowid IN (
9280: SELECT dist.rowid
9281: FROM ap_invoices inv, ap_self_assessed_tax_dist_all dist
9282: WHERE inv.invoice_id = dist.invoice_id
9283: AND ( dist.line_type_lookup_code in ('ITEM','ACCRUAL','RETROACCRUAL','NONREC_TAX')
9284: OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
9285: AND EXISTS (SELECT 'X'
9294: AND si.organization_id = po_dist1.org_id)
9295: )
9296: ) --Bug#5399352. Added this here to take care of IPV/TIPV records matched to EIB item PO.
9297: AND NOT EXISTS (SELECT NULL
9298: FROM ap_self_assessed_tax_dist_all apdist2
9299: WHERE apdist2.pa_addition_flag = 'Y'
9300: AND apdist2.po_distribution_id = dist.po_distribution_id
9301: AND apdist2.line_type_lookup_code in ('ITEM','ACCRUAL','RETROACCRUAL','NONREC_TAX')
9302: )
9300: AND apdist2.po_distribution_id = dist.po_distribution_id
9301: AND apdist2.line_type_lookup_code in ('ITEM','ACCRUAL','RETROACCRUAL','NONREC_TAX')
9302: )
9303: AND ( EXISTS (SELECT NULL
9304: FROM ap_self_assessed_tax_dist_all apdist1,
9305: ap_invoices_all ap1 /*Changes for bug 7650946 -- Start
9306: WHERE apdist1.pa_addition_flag IN ('F', 'G')
9307: AND ap1.invoice_id = apdist1.invoice_id
9308: AND apdist1.po_distribution_id = dist.po_distribution_id
13156: AND apdist.line_type_lookup_code in ('ITEM','ACCRUAL','RETROACCRUAL')
13157: AND apdist.pa_addition_flag = 'Y' /*Bug 13602288: Self Assessed Tax Changes*/
13158: /* UNION ALL /*Bug 13602288: Self Assessed Tax Changes*/
13159: /* SELECT null
13160: FROM ap_self_assessed_tax_dist apdist
13161: WHERE apdist.po_distribution_id = P_Po_Distribution_Id
13162: AND apdist.line_type_lookup_code in ('ITEM','ACCRUAL','RETROACCRUAL','NONREC_TAX')
13163: AND apdist.pa_addition_flag = 'Y' Removed the union for the bug 13984115*/
13164: );