[Home] [Help]
1367:
1368: l_progress := '027';
1369: x_return_status := FND_API.G_RET_STS_SUCCESS;
1370:
1371: -- LCM ER start. If all the submission checks have been passed successfully then update the lcm_flag of po_line_locations_all and po_distributions_all.
1372: IF p_document_type in ('PO','RELEASE') THEN
1373:
1374: FOR ship_rec in (select line_location_id from po_line_locations_gt)
1375:
2293: BEGIN
2294:
2295: l_progress := '000';
2296: -- BUG 2687600 mbhargav
2297: --Removed update statement to update rate in po_distributions
2298:
2299: l_progress := '001';
2300: IF g_debug_stmt THEN
2301: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2721: , l_ship_amt_tbl --
2722: , l_dist_qty_tbl --
2723: , l_dist_amt_tbl --
2724: FROM
2725: PO_DISTRIBUTIONS_GT POD --
2727: WHERE
2728: PLL.line_location_id = POD.line_location_id
2729: AND PLL.po_release_id = p_document_id
2731: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
2732: AND ( --
2733: ( ( PLL.quantity IS NOT NULL )
2734: AND ( PLL.quantity <> ( SELECT sum(POD2.quantity_ordered)
2735: FROM PO_DISTRIBUTIONS_GT POD2
2736: WHERE POD2.line_location_id = PLL.line_location_id ) ) )
2737: OR ( ( PLL.amount IS NOT NULL )
2738: AND ( PLL.amount <> ( SELECT sum(POD2.amount_ordered)
2739: FROM PO_DISTRIBUTIONS_GT POD2
2735: FROM PO_DISTRIBUTIONS_GT POD2
2736: WHERE POD2.line_location_id = PLL.line_location_id ) ) )
2737: OR ( ( PLL.amount IS NOT NULL )
2738: AND ( PLL.amount <> ( SELECT sum(POD2.amount_ordered)
2739: FROM PO_DISTRIBUTIONS_GT POD2
2740: WHERE POD2.line_location_id = PLL.line_location_id ) ) )
2741: )
2742: GROUP BY
2743: PLL.shipment_num
2838: AND nvl(PLL.cancel_flag,'N') = 'N'
2839: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
2840: AND NOT EXISTS
2841: (SELECT 'Distribution Exists'
2842: FROM PO_DISTRIBUTIONS_GT POD --
2844:
2845:
2846: --Increment the p_sequence with number of errors reported in last query
2885: substr(g_shipmsg||g_delim||POLL.shipment_num||g_delim
2886: ||g_distmsg||g_delim||POD.distribution_num||g_delim
2887: ||l_textline,1,240),
2888: 'PO_SUB_REL_RATE_NULL'
2889: FROM PO_DISTRIBUTIONS_GT POD, --
2891: PO_RELEASES_GT POR,PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,
2892: FINANCIALS_SYSTEM_PARAMETERS FSP
2893: WHERE POLL.po_release_id = POR.po_release_id
3935: POD.distribution_num||g_delim||l_textline, 1,240),
3936: 'PO_SUB_REL_DIST_DEST_TYPE_NE_I'
3937: FROM PO_RELEASES_GT POR,
3938: PO_LINE_LOCATIONS_GT PLL,
3939: PO_DISTRIBUTIONS_GT POD
3940: WHERE POR.po_release_id = POD.po_release_id
3941: AND POD.line_location_id = PLL.line_location_id
3942: AND POR.po_release_id = p_document_id
3943: AND Nvl(POD.LCM_FLAG,'N') = 'Y'
4079: nvl( POLL.line_location_id,0) line_loc_id
4080: FROM PO_LINE_LOCATIONS_GT POLL,
4081: PO_LINE_TYPES_B PLT, -- bug3413891
4082: PO_LINES_GT POL,
4083: PO_DISTRIBUTIONS_GT POD
4084: WHERE POLL.po_line_id = POL.po_line_id
4085: AND POLL.line_location_id = POD.line_location_id
4086: AND POLL.po_header_id = p_document_id
4087: AND POL.line_type_id = PLT.line_type_id
4113: nvl(POL.item_id,-1) item_id,
4114: POLL.line_location_id line_loc_id
4115: FROM PO_LINE_LOCATIONS_GT POLL,
4116: PO_LINES_GT POL,
4117: PO_DISTRIBUTIONS_GT POD
4118: WHERE POL.po_header_id = p_document_id
4119: AND POD.line_location_id = POLL.line_location_id
4120: AND POLL.line_location_id =
4121: (SELECT min(POLL2.line_location_id)
4160: nvl( POLL.line_location_id,0) line_loc_id
4161: FROM PO_LINE_LOCATIONS_GT POLL,
4162: PO_LINE_TYPES_B PLT, -- bug3413891
4163: PO_LINES POL,
4164: PO_DISTRIBUTIONS_GT POD --
4166: AND POLL.line_location_id = POD.line_location_id
4167: AND POLL.po_release_id = p_document_id
4168: AND POL.line_type_id = PLT.line_type_id
4197: min(PRL.org_id) --
4198: FROM PO_REQUISITION_LINES_ALL PRL, --
4199: PO_LINE_LOCATIONS_GT POLL,
4200: PO_LINES POL,
4201: PO_DISTRIBUTIONS PD,
4202: PO_REQ_DISTRIBUTIONS_ALL PRD --
4203: WHERE POLL.line_location_id = p_line_location_id
4204: AND POLL.po_line_id = POL.po_line_id
4205: AND PRL.unit_price >= 0
5582: * p_check_asl: Determines whether to perform ASL checks...
5583: * PO_SUB_ITEM_NOT_APPROVED, PO_SUB_ITEM_ASL_DEBARRED
5584: * IN OUT PARAMETERS
5585: * p_sequence: Sequence number of last reported error
5586: * Modifies: Updates PO_DISTRIBUTIONS table with RATE information.
5587: * Inserts error msgs in online_report_text_gt table, uses
5588: * global_temp tables for processing
5589: * Effects: This procedure runs the document submission checks for POs
5590: * Returns:
5639: BEGIN
5640:
5641: l_progress := '000';
5642: -- BUG 2687600 mbhargav
5643: --Removed Update statement to update rate in po_distributions
5644:
5645: l_progress := '001';
5646: IF g_debug_stmt THEN
5647: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
5801: nvl(POL.cancel_flag, 'N') = 'N' AND
5802: nvl(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
5803: PLL.shipment_type in ('STANDARD', 'PLANNED', 'PREPAYMENT') --
5804: AND NOT EXISTS (SELECT 'Distribution Exists'
5805: FROM PO_DISTRIBUTIONS_GT POD
5806: WHERE POD.line_location_id = PLL.line_location_id);
5807:
5808: --Increment the p_sequence with number of errors reported in last query
5809: p_sequence := p_sequence + SQL%ROWCOUNT;
5874: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
5875: ||g_distmsg||g_delim||POD.distribution_num||g_delim
5876: ||l_textline,1,240),
5877: 'PO_SUB_DIST_RATE_NULL'
5878: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,
5879: PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP
5880: WHERE POD.po_header_id = POH.po_header_id
5881: AND POD.line_location_id = PLL.line_location_id
5882: AND PLL.po_line_id = POL.po_line_id
5933: ||g_shipmsg||g_delim||PLL.shipment_num||g_delim
5934: ||g_distmsg||g_delim||POD.distribution_num||g_delim
5935: ||l_textline,1,240),
5936: 'PO_SUB_DIST_RATE_NOT_NULL'
5937: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL,PO_LINES_GT POL,
5938: PO_HEADERS_GT POH,GL_SETS_OF_BOOKS SOB,FINANCIALS_SYSTEM_PARAMETERS FSP
5939: WHERE POD.po_header_id = POH.po_header_id
5940: AND POD.line_location_id = PLL.line_location_id
5941: AND PLL.po_line_id = POL.po_line_id
7145: 'LINE_NUM',pol.line_num,
7146: 'SHIP_NUM',pos.shipment_num,
7147: 'DIST_NUM',pod.distribution_num),
7148: 'PO_INVALID_OKE_CONTRACT_LNE_ID'
7149: FROM po_distributions_gt pod,
7150: po_line_locations_gt pos,
7151: po_lines_gt pol
7152: WHERE pod.po_line_id = pol.po_line_id
7153: AND pod.line_location_id = pos.line_location_id
7193: 'LINE_NUM',pol.line_num,
7194: 'SHIP_NUM',pos.shipment_num,
7195: 'DIST_NUM',pod.distribution_num),
7196: 'PO_INVALID_OKE_CONTRACT_DLV_ID'
7197: FROM po_distributions_gt pod,
7198: po_line_locations_gt pos,
7199: po_lines_gt pol
7200: WHERE pod.po_line_id = pol.po_line_id
7201: AND pod.line_location_id = pos.line_location_id
7301: 'PO_SUB_PO_DIST_DEST_TYPE_NE_IN'
7302: FROM PO_HEADERS_GT POH,
7303: PO_LINES_GT POL,
7304: PO_LINE_LOCATIONS_GT PLL,
7305: PO_DISTRIBUTIONS_GT POD
7306: WHERE POH.po_header_id = POD.po_header_id
7307: AND POD.line_location_id = PLL.line_location_id
7308: AND PLL.po_line_id = POL.po_line_id
7309: AND POH.po_header_id = p_document_id
8393: SELECT count('Y')
8394: INTO l_count_expense_dist
8395: FROM DUAL
8396: WHERE EXISTS(SELECT 'Y'
8397: FROM PO_DISTRIBUTIONS_GT
8398: WHERE LINE_LOCATION_ID = l_line_location_id(i)
8399: AND DESTINATION_TYPE_CODE = 'EXPENSE');
8400:
8401: IF(l_count_expense_dist <= 0)
10134:
10135: DELETE FROM po_headers_gt;
10136: DELETE FROM po_lines_gt;
10137: DELETE FROM po_line_locations_gt;
10138: DELETE FROM po_distributions_gt;
10139: DELETE FROM po_releases_gt;
10140: DELETE FROM po_req_headers_gt;
10141: DELETE FROM po_req_lines_gt;
10142: DELETE FROM po_req_distributions_gt;
10782: -- in values or if NULL then with existing values in the table
10783: -- SQL Why: Need to apply requested line level changes to global temp table
10784: -- SQL Join: po_line_id
10785: FORALL i IN 1..p_requested_changes.distribution_changes.po_distribution_id.COUNT
10786: UPDATE po_distributions_gt
10787: SET quantity_ordered = nvl(p_requested_changes.distribution_changes.quantity_ordered(i),
10788: quantity_ordered),
10789: --
10790: -- Added amount_ordered as a changeable field:
13819: , NULL , 'PO_SUB_DIST_BILL_GT_ORD_AMT'
13820: , 'PO_SUB_DIST_BILL_GT_ORD_QTY'
13821: )
13822: FROM
13823: PO_DISTRIBUTIONS_GT POD
13824: , PO_LINE_LOCATIONS_GT POLL
13825: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
13826: WHERE POD.line_location_id = POLL.line_location_id
13827: AND POL.po_line_id = POLL.po_line_id
13907: , NULL , 'PO_SUB_DIST_DLVR_GT_ORD_AMT'
13908: , 'PO_SUB_DIST_DLVR_GT_ORD_QTY'
13909: )
13910: FROM
13911: PO_DISTRIBUTIONS_GT POD
13912: , PO_LINE_LOCATIONS_GT POLL
13913: , PO_LINES_ALL POL -- For releases, PO_LINES_GT table isn't populated.
13914: WHERE POD.line_location_id = POLL.line_location_id
13915: AND POL.po_line_id = POLL.po_line_id
14428:
14429: l_progress := '050';
14430:
14431: FORALL i IN 1 .. l_dist_id_tbl.COUNT
14432: INSERT INTO PO_DISTRIBUTIONS_GT
14433: (
14434: po_distribution_id ,
14435: last_update_date ,
14436: last_updated_by ,
14626: oke_contract_line_id ,
14627: oke_contract_deliverable_id
14628: , distribution_type
14629: , amount_to_encumber
14630: FROM PO_DISTRIBUTIONS_ALL POD
14631: WHERE POD.po_distribution_id = l_dist_id_tbl(i)
14632: ;
14633:
14634: l_progress := '060';
14809: , l_line_num_tbl
14810: , l_shipment_num_tbl
14811: , l_distribution_num_tbl
14812: FROM
14813: PO_DISTRIBUTIONS_GT POD
14814: , PO_LINE_LOCATIONS_GT POLL
14815: , PO_LINES_ALL POL -- For Releases, PO_LINES_GT doesn't get populated.
14816: , PO_HEADERS_GT POH
14817: WHERE POLL.line_location_id(+) = POD.line_location_id --JOIN
16083: , 'QTY1', sum(NVL(pod.quantity_delivered, 0))
16084: )
16085: ), 1, 240)
16086: , 'PO_CAN_POLL_REC_NOT_DEL'
16087: FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod
16088: WHERE pod.line_location_id = poll.line_location_id
16089: AND pol.po_line_id = poll.po_line_id
16090: AND pol.order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
16091: AND NVL(poll.cancel_flag, 'N') = 'N'
16092: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
16093: AND NVL(poll.quantity_received, 0) >
16094: (
16095: SELECT sum(NVL(pod2.quantity_delivered, 0))
16096: FROM po_distributions_gt pod2
16097: WHERE pod2.line_location_id = poll.line_location_id
16098: )
16099: GROUP BY pol.line_num
16100: , poll.shipment_num
16206: , 'QTY1', sum(NVL(pod.amount_delivered, 0))
16207: )
16208: ), 1, 240)
16209: , 'PO_CAN_POLL_AMT_REC_NOT_DEL'
16210: FROM po_lines_gt pol, po_line_locations_gt poll, po_distributions_gt pod
16211: WHERE pod.line_location_id = poll.line_location_id
16212: AND pol.po_line_id = poll.po_line_id
16213: AND pol.order_type_lookup_code IN ('RATE', 'FIXED PRICE')
16214: AND NVL(poll.cancel_flag, 'N') = 'N'
16215: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
16216: AND NVL(poll.amount_received, 0) >
16217: (
16218: SELECT sum(NVL(pod2.amount_delivered, 0))
16219: FROM po_distributions_gt pod2
16220: WHERE pod2.line_location_id = poll.line_location_id
16221: )
16222: GROUP BY pol.line_num
16223: , poll.shipment_num
16342: || poll.shipment_num || g_delim || g_distmsg || g_delim || l_textline
16343: ), 1, 240)
16344: , 'PO_CAN_POLL_INVALID_ACCT_FLEX'
16345: FROM po_lines_gt pol, po_line_locations_gt poll
16346: , po_distributions_gt pod, gl_code_combinations gcc
16347: WHERE pod.line_location_id = poll.line_location_id
16348: AND pol.po_line_id = poll.po_line_id
16349: AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'PREPAYMENT') --
16350: AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
17131: , l_lineloc_qty_tbl
17132: , l_lineloc_amt_tbl
17133: , l_dist_qty_tbl
17134: , l_dist_amt_tbl
17135: FROM PO_DISTRIBUTIONS_GT POD,PO_LINE_LOCATIONS_GT PLL, PO_LINES_GT POL
17136: WHERE PLL.po_line_id = POL.po_line_id
17137: AND POD.line_location_id = PLL.line_location_id
17138: AND PLL.po_header_id = p_document_id
17139: AND nvl(PLL.cancel_flag,'N') = 'N'
17584: , distribution_num
17585: , p_sequence + ROWNUM
17586: , substr(l_textline, 1, 240)
17587: , l_message_name
17588: FROM po_distributions_gt pod
17589: WHERE pod.po_distribution_id=p_doc_level_id
17590: AND chk_unv_invoices(l_invoice_type, pod.po_header_id, pod.po_release_id, NULL,NULL, pod.po_distribution_id, p_origin_doc_id, l_calling_sequence) = 1;
17591: END IF; --
17592:
17661: WHERE line_location_id=p_doc_level_id;
17662: ELSIF p_document_level = g_document_level_DISTRIBUTION THEN
17663: SELECT po_header_id
17664: INTO l_document_id
17665: FROM po_distributions_gt
17666: WHERE po_distribution_id=p_doc_level_id;
17667: END IF;
17668: IF p_document_type <> g_document_type_RELEASE THEN
17669: l_is_complex_po := PO_COMPLEX_WORK_PVT.is_complex_work_po(l_document_id);
17823: SET lcm_flag = 'Y'
17824: WHERE line_location_id = p_line_location_id
17825: and lcm_flag is null;
17826:
17827: UPDATE po_distributions_gt
17828: SET lcm_flag = 'Y'
17829: WHERE line_location_id = p_line_location_id
17830: and lcm_flag is null;
17831:
17835: UPDATE po_line_locations_all
17836: SET lcm_flag = 'Y'
17837: WHERE line_location_id = p_line_location_id;
17838:
17839: UPDATE po_distributions_all
17840: SET lcm_flag = 'Y'
17841: WHERE line_location_id = p_line_location_id;
17842: END IF;
17843:
17847: SET lcm_flag = null
17848: WHERE line_location_id = p_line_location_id
17849: AND lcm_flag = 'Y';
17850:
17851: UPDATE po_distributions_all
17852: SET lcm_flag = null
17853: WHERE line_location_id = p_line_location_id
17854: AND lcm_flag = 'Y';
17855: