[Home] [Help]
2617: AND (
2618: EXISTS (
2619: SELECT NULL
2620: FROM ap_invoices_all inv,
2621: po_distributions_all PO,
2622: ap_invoice_distributions_all dist2
2623: WHERE inv.invoice_id = dist2.invoice_id
2624: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
2625: AND dist2.invoice_id = DIST.invoice_id
2651: /* Bug 6353803: Added the following for this bug. */
2652: AND (pa_nl_installed.is_nl_installed = 'N'
2653: OR ( pa_nl_installed.is_nl_installed = 'Y'
2654: AND NOT EXISTS (SELECT 'X'
2655: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2656: WHERE pod.po_distribution_id = dist.po_distribution_id
2657: AND pod.po_line_id = pol.po_line_id
2658: AND si.inventory_item_id = pol.item_id
2659: AND si.comms_nl_trackable_flag = 'Y'
2685: )
2686: )
2687: )/* Start changes for bug#14320909 */
2688: AND NOT EXISTS (select 'X' from rcv_receiving_sub_ledger rcv_sub,
2689: po_distributions_all po_dist1
2690: where po_dist1.CODE_COMBINATION_ID =rcv_sub.CODE_COMBINATION_ID
2691: AND rcv_sub.ACTUAL_FLAG = 'A'
2692: and rcv_sub.pa_addition_flag ='Y'
2693: AND to_char(DIST.po_distribution_id) = rcv_sub.reference3
2743: /* Bug 6353803: Added the following for this bug. */
2744: AND (pa_nl_installed.is_nl_installed = 'N'
2745: OR ( pa_nl_installed.is_nl_installed = 'Y'
2746: AND NOT EXISTS (SELECT 'X'
2747: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2748: WHERE pod.po_distribution_id = dist.po_distribution_id
2749: AND pod.po_line_id = pol.po_line_id
2750: AND si.inventory_item_id = pol.item_id
2751: AND si.comms_nl_trackable_flag = 'Y'
2826: AND aid.project_id = G_PROJECT_ID)
2827: AND (pa_nl_installed.is_nl_installed = 'N'
2828: OR ( pa_nl_installed.is_nl_installed = 'Y'
2829: AND NOT EXISTS (SELECT 'X'
2830: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2831: WHERE pod.po_distribution_id = dist.po_distribution_id
2832: AND pod.po_line_id = pol.po_line_id
2833: AND si.inventory_item_id = pol.item_id
2834: AND si.comms_nl_trackable_flag = 'Y'
2889: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
2890: AND EXISTS (
2891: SELECT NULL
2892: FROM ap_invoices_all inv,
2893: po_distributions_all PO,
2894: ap_invoice_distributions_all dist2
2895: WHERE inv.invoice_id = dist2.invoice_id
2896: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
2897: AND dist2.invoice_id = DIST.invoice_id
2911: /* Bug 6353803: Added the following for this bug. */
2912: AND (pa_nl_installed.is_nl_installed = 'N'
2913: OR ( pa_nl_installed.is_nl_installed = 'Y'
2914: AND NOT EXISTS (SELECT 'X'
2915: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2916: WHERE pod.po_distribution_id = dist.po_distribution_id
2917: AND pod.po_line_id = pol.po_line_id
2918: AND si.inventory_item_id = pol.item_id
2919: AND si.comms_nl_trackable_flag = 'Y'
2971: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
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
2992: )
2993: AND (pa_nl_installed.is_nl_installed = 'N'
2994: OR ( pa_nl_installed.is_nl_installed = 'Y'
2995: AND NOT EXISTS (SELECT 'X'
2996: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
2997: WHERE pod.po_distribution_id = dist.po_distribution_id
2998: AND pod.po_line_id = pol.po_line_id
2999: AND si.inventory_item_id = pol.item_id
3000: AND si.comms_nl_trackable_flag = 'Y'
3062: AND (
3063: EXISTS (
3064: SELECT NULL
3065: FROM ap_invoices_all inv,
3066: po_distributions_all PO,
3067: ap_invoice_distributions_all dist2
3068: WHERE inv.invoice_id = dist2.invoice_id
3069: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
3070: AND dist2.invoice_id = DIST.invoice_id
3095: /* Bug 6353803: Added the following for this bug. */
3096: AND (pa_nl_installed.is_nl_installed = 'N'
3097: OR ( pa_nl_installed.is_nl_installed = 'Y'
3098: AND NOT EXISTS (SELECT 'X'
3099: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
3100: WHERE pod.po_distribution_id = dist.po_distribution_id
3101: AND pod.po_line_id = pol.po_line_id
3102: AND si.inventory_item_id = pol.item_id
3103: AND si.comms_nl_trackable_flag = 'Y'
3129: )
3130: )
3131: )/* Start changes for bug#14320909 */
3132: AND NOT EXISTS (select 'X' from rcv_receiving_sub_ledger rcv_sub,
3133: po_distributions_all po_dist1
3134: where po_dist1.CODE_COMBINATION_ID =rcv_sub.CODE_COMBINATION_ID
3135: AND rcv_sub.ACTUAL_FLAG = 'A'
3136: and rcv_sub.pa_addition_flag ='Y'
3137: AND to_char(DIST.po_distribution_id) = rcv_sub.reference3
3186: /* Bug 6353803: Added the following for this bug. */
3187: AND (pa_nl_installed.is_nl_installed = 'N'
3188: OR ( pa_nl_installed.is_nl_installed = 'Y'
3189: AND NOT EXISTS (SELECT 'X'
3190: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
3191: WHERE pod.po_distribution_id = dist.po_distribution_id
3192: AND pod.po_line_id = pol.po_line_id
3193: AND si.inventory_item_id = pol.item_id
3194: AND si.comms_nl_trackable_flag = 'Y'
3267: AND aid.project_id > 0 )
3268: AND (pa_nl_installed.is_nl_installed = 'N'
3269: OR ( pa_nl_installed.is_nl_installed = 'Y'
3270: AND NOT EXISTS (SELECT 'X'
3271: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
3272: WHERE pod.po_distribution_id = dist.po_distribution_id
3273: AND pod.po_line_id = pol.po_line_id
3274: AND si.inventory_item_id = pol.item_id
3275: AND si.comms_nl_trackable_flag = 'Y'
3330: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
3331: AND EXISTS (
3332: SELECT NULL
3333: FROM ap_invoices_all inv,
3334: po_distributions_all PO,
3335: ap_invoice_distributions_all dist2
3336: WHERE inv.invoice_id = dist2.invoice_id
3337: AND nvl(po.distribution_type,'XXX') <> 'PREPAYMENT'
3338: AND dist2.invoice_id = DIST.invoice_id
3353: /* Bug 6353803: Added the following for this bug. */
3354: AND (pa_nl_installed.is_nl_installed = 'N'
3355: OR ( pa_nl_installed.is_nl_installed = 'Y'
3356: AND NOT EXISTS (SELECT 'X'
3357: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
3358: WHERE pod.po_distribution_id = dist.po_distribution_id
3359: AND dist.po_distribution_id > 0 /*Added for bug#12922795 */
3360: AND pod.po_line_id = pol.po_line_id
3361: AND si.inventory_item_id = pol.item_id
3415: AND dist.line_type_lookup_code <> 'REC_TAX' -- do not proces recoverable tax
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
3435: )
3436: AND (pa_nl_installed.is_nl_installed = 'N'
3437: OR ( pa_nl_installed.is_nl_installed = 'Y'
3438: AND NOT EXISTS (SELECT 'X'
3439: FROM po_distributions_all pod, mtl_system_items si, po_lines_all pol
3440: WHERE pod.po_distribution_id = dist.po_distribution_id
3441: AND pod.po_line_id = pol.po_line_id
3442: AND si.inventory_item_id = pol.item_id
3443: AND si.comms_nl_trackable_flag = 'Y'
5435: UPDATE rcv_receiving_sub_ledger rcv_sub
5436: SET rcv_sub.pa_addition_flag = 'L'
5437: WHERE rcv_sub.pa_addition_flag = 'N'
5438: and reference3 = TO_CHAR(p_po_distribution_id) and exists (
5439: select 1 from po_distributions_all pod
5440: where po_distribution_id = TO_NUMBER(rcv_sub.reference3)
5441: and po_distribution_id = p_po_distribution_id
5442: and code_combination_id = rcv_sub.code_combination_id
5443: and accrue_on_receipt_flag = 'Y');
5649: and hist.transaction_type in ( 'PAYMENT CREATED','PAYMENT CANCELLED') /* 'PAYMENT CREATED' Added as part of Bug 10626450 ,'PAYMENT CANCELLED' as part of 11703565 */
5650: AND hist.posted_flag = 'Y')
5651: AND exists(SELECT inv.invoice_id -- 11658939 hint del no_unnest
5652: FROM AP_INVOICES_ALL inv,
5653: PO_Distributions_all PO,
5654: AP_Invoice_Distributions_all aid,
5655: ap_invoice_payments_all aip
5656: WHERE inv.invoice_id = aip.invoice_id
5657: AND aid.invoice_id = inv.invoice_id
5708: and hist.transaction_type not in ( 'PAYMENT CREATED','PAYMENT CANCELLED') /* 'PAYMENT CREATED' Added as part of Bug 10626450 ,'PAYMENT CANCELLED' as part of 11703565 */
5709: AND hist.posted_flag = 'Y')
5710: AND exists(SELECT inv.invoice_id -- 11658939 hint del no_unnest
5711: FROM AP_INVOICES_ALL inv,
5712: PO_Distributions_all PO,
5713: AP_Invoice_Distributions_all aid,
5714: ap_invoice_payments_all aip
5715: WHERE inv.invoice_id = aip.invoice_id
5716: AND aid.invoice_id = inv.invoice_id
5802: and hist.transaction_type in ( 'PAYMENT CREATED','PAYMENT CANCELLED') /* 'PAYMENT CREATED' Added as part of Bug 10626450 ,'PAYMENT CANCELLED' as part of 11703565 */
5803: AND hist.posted_flag = 'Y')
5804: AND exists(SELECT inv.invoice_id -- 11658939 hint del no_unnest
5805: FROM AP_INVOICES_ALL inv,
5806: PO_Distributions_all PO,
5807: AP_Invoice_Distributions_all aid,
5808: ap_invoice_payments_all aip
5809: WHERE inv.invoice_id = aip.invoice_id
5810: AND aid.invoice_id = inv.invoice_id
5860: and hist.transaction_type not in ( 'PAYMENT CREATED','PAYMENT CANCELLED') /* 'PAYMENT CREATED' Added as part of Bug 10626450 ,'PAYMENT CANCELLED' as part of 11703565 */
5861: AND hist.posted_flag = 'Y')
5862: AND exists(SELECT inv.invoice_id -- 11658939 hint del no_unnest
5863: FROM AP_INVOICES_ALL inv,
5864: PO_Distributions_all PO,
5865: AP_Invoice_Distributions_all aid,
5866: ap_invoice_payments_all aip
5867: WHERE inv.invoice_id = aip.invoice_id
5868: AND aid.invoice_id = inv.invoice_id
8980: OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
8981: AND EXISTS (SELECT 'X'
8982: FROM mtl_system_items si,
8983: po_lines_all pol,
8984: po_distributions_all po_dist1
8985: WHERE po_dist1.po_line_id = pol.po_line_id
8986: AND po_dist1.po_distribution_id = dist.po_distribution_id
8987: AND si.inventory_item_id = pol.item_id
8988: AND po_dist1.project_id IS NOT NULL
9062: OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
9063: AND EXISTS (SELECT 'X'
9064: FROM mtl_system_items si,
9065: po_lines_all pol,
9066: po_distributions_all po_dist1
9067: WHERE po_dist1.po_line_id = pol.po_line_id
9068: AND po_dist1.po_distribution_id = dist.po_distribution_id
9069: AND si.inventory_item_id = pol.item_id
9070: AND po_dist1.project_id IS NOT NULL
9201: OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
9202: AND EXISTS (SELECT 'X'
9203: FROM mtl_system_items si,
9204: po_lines_all pol,
9205: po_distributions_all po_dist1
9206: WHERE po_dist1.po_line_id = pol.po_line_id
9207: AND po_dist1.po_distribution_id = dist.po_distribution_id
9208: AND si.inventory_item_id = pol.item_id
9209: AND po_dist1.project_id IS NOT NULL
9284: OR ( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
9285: AND EXISTS (SELECT 'X'
9286: FROM mtl_system_items si,
9287: po_lines_all pol,
9288: po_distributions_all po_dist1
9289: WHERE po_dist1.po_line_id = pol.po_line_id
9290: AND po_dist1.po_distribution_id = dist.po_distribution_id
9291: AND si.inventory_item_id = pol.item_id
9292: AND po_dist1.project_id IS NOT NULL
10669: AND dist.amount <>0
10670: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
10671: FROM AP_INVOICES_ALL inv,
10672: PO_VENDORS vend,
10673: PO_Distributions_all PO,
10674: AP_Invoice_Distributions_all aid, --STD INV DIST LINE
10675: AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
10676: WHERE aid.invoice_id = inv.invoice_id
10677: AND inv.vendor_id = vend.vendor_id
10760: AND dist.amount <>0
10761: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
10762: FROM AP_INVOICES_ALL inv,
10763: PO_VENDORS vend,
10764: PO_Distributions_all PO,
10765: AP_Invoice_Distributions_all aid, --STD INV DIST LINE
10766: AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
10767: WHERE aid.invoice_id = inv.invoice_id
10768: AND inv.vendor_id = vend.vendor_id
10823: WHERE hist.payment_history_id = dist.payment_history_id
10824: AND hist.posted_flag = 'Y')
10825: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
10826: FROM AP_INVOICES_ALL inv,
10827: PO_Distributions_all PO,
10828: AP_Invoice_Distributions_all aid,
10829: ap_invoice_payments_all aip
10830: WHERE inv.invoice_id = aip.invoice_id
10831: AND aid.invoice_id = inv.invoice_id
10870: WHERE nvl(dist.pa_addition_flag,'N') = 'N'
10871: AND dist.amount <> 0
10872: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
10873: FROM AP_INVOICES_ALL inv,
10874: PO_Distributions_all PO,
10875: AP_Invoice_Distributions_all aid, --STD INV DIST LINE
10876: AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
10877: WHERE aid.invoice_id = inv.invoice_id
10878: AND aid.invoice_distribution_id = dist.invoice_distribution_id -- Std inv line
10925: WHERE hist.payment_history_id = dist.payment_history_id
10926: AND hist.posted_flag = 'Y')
10927: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
10928: FROM AP_INVOICES_ALL inv,
10929: PO_Distributions_all PO,
10930: AP_Invoice_Distributions_all aid,
10931: ap_invoice_payments_all aip
10932: WHERE inv.invoice_id = aip.invoice_id
10933: AND aid.invoice_id = inv.invoice_id
10972: WHERE nvl(dist.pa_addition_flag,'N') = 'N'
10973: AND dist.amount <> 0
10974: AND exists(SELECT /*+ no_unnest */ inv.invoice_id
10975: FROM AP_INVOICES_ALL inv,
10976: PO_Distributions_all PO,
10977: AP_Invoice_Distributions_all aid, --STD INV DIST LINE
10978: AP_Invoice_Distributions_all aid2 -- PREPAY APPL DIST LINE
10979: WHERE aid.invoice_id = inv.invoice_id
10980: AND inv.org_id = G_ORG_ID