[Home] [Help]
4: -----------------------------------------------------------------------
5: --Start of Comments
6: --Name: Validate_Requisition_Grp
7: --Pre-reqs:
8: --Modifies: po_requisitions_interface
9: --Locks:
10: -- None
11: --Function: validates OPM columns of interface records in po_requisitions_interface
12: --Parameters:
7: --Pre-reqs:
8: --Modifies: po_requisitions_interface
9: --Locks:
10: -- None
11: --Function: validates OPM columns of interface records in po_requisitions_interface
12: --Parameters:
13: --IN:
14: --p_api_version
15: -- API Version the caller thinks this API is on
95: pri.secondary_uom_code,
96: pri.secondary_quantity,
97: pri.quantity,
98: pri.preferred_grade
99: From po_requisitions_interface pri
100: Where pri.request_id = p_request_id
101: FOR UPDATE OF pri.secondary_unit_of_measure;
102:
103: Cursor Cr_opm_item_attr(p_inv_item_id IN NUMBER,p_organization_id IN NUMBER) IS
195: IF NOT v_opm_item and v_process_dest_org = 'Y' THEN
196: l_rec.interface_type := 'REQIMPORT';
197: l_rec.interface_transaction_id := cr_rec.transaction_id;
198: l_rec.column_name := 'ITEM_ID';
199: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
200: l_rec.error_message_name := 'GML_OPM_ITEM_NOT_EXIST';
201:
202: fnd_message.set_name('GML', l_rec.error_message_name);
203: l_rec.error_message := FND_MESSAGE.get;
233: THEN
234: l_rec.interface_type := 'REQIMPORT';
235: l_rec.interface_transaction_id := cr_rec.transaction_id;
236: l_rec.column_name := 'SOURCE_ORGANIZATION_ID';
237: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
238: l_rec.error_message_name := 'GML_INVALID_ORG_TYPE_COMB';
239:
240: fnd_message.set_name('GML', l_rec.error_message_name);
241: l_rec.error_message := FND_MESSAGE.get;
262: THEN
263: l_rec.interface_type := 'REQIMPORT';
264: l_rec.interface_transaction_id := cr_rec.transaction_id;
265: l_rec.column_name := 'SOURCE_ORGANIZATION_ID';
266: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
267: l_rec.error_message_name := 'GML_SAME_SOURCE_DEST_ORG';
268:
269: fnd_message.set_name('GML', l_rec.error_message_name);
270: l_rec.error_message := FND_MESSAGE.get;
320: IF (l_passed_secondary_uom <> l_secondary_unit_of_measure) OR v_uom_error THEN
321: l_rec.interface_type := 'REQIMPORT';
322: l_rec.interface_transaction_id := cr_rec.transaction_id;
323: l_rec.column_name := 'SECONDARY_UOM_CODE';
324: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
325: l_rec.error_message_name := 'GML_INVALID_SECONDARY_UOM';
326:
327: fnd_message.set_name('GML', l_rec.error_message_name);
328: l_rec.error_message := FND_MESSAGE.get;
347:
348: ELSE
349:
350: --if uom code is correct update the unit of measure.
351: UPDATE po_requisitions_interface pri
352: SET pri.secondary_unit_of_measure = l_secondary_unit_of_measure
353: WHERE rowid = cr_rec.rowid;
354:
355: END IF;
360: THEN
361:
362: l_secondary_unit_of_measure := po_gml_db_common.get_apps_uom_code(v_item_um2);
363:
364: UPDATE po_requisitions_interface pri
365: SET pri.secondary_unit_of_measure = l_secondary_unit_of_measure
366: WHERE rowid = cr_rec.rowid;
367:
368: --If unit of measure is provided then validate it.
376: --log error that unit of measure is not correct.
377: l_rec.interface_type := 'REQIMPORT';
378: l_rec.interface_transaction_id := cr_rec.transaction_id;
379: l_rec.column_name := 'SECONDARY_UNIT_OF_MEASURE';
380: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
381: l_rec.error_message_name := 'GML_INVALID_SECONDARY_UOM';
382:
383: fnd_message.set_name('GML', l_rec.error_message_name);
384: l_rec.error_message := FND_MESSAGE.get;
415: v_item_um,
416: v_item_um2,
417: l_secondary_quantity) ;
418:
419: UPDATE po_requisitions_interface
420: SET secondary_unit_of_measure = nvl(secondary_unit_of_measure,l_secondary_unit_of_measure),
421: secondary_quantity = l_secondary_quantity
422: WHERE rowid = cr_rec.rowid;
423:
440: IF ( l_ret_val = -68 ) THEN
441: l_rec.interface_type := 'REQIMPORT';
442: l_rec.interface_transaction_id := cr_rec.transaction_id;
443: l_rec.column_name := 'SECONDARY_QUANTITY';
444: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
445: l_rec.error_message_name := 'IC_DEVIATION_HI_ERR';
446:
447: fnd_message.set_name('GMI', l_rec.error_message_name);
448: l_rec.error_message := FND_MESSAGE.get;
465: ELSIF ( l_ret_val = -69 ) OR cr_rec.secondary_quantity <= 0 THEN
466: l_rec.interface_type := 'REQIMPORT';
467: l_rec.interface_transaction_id := cr_rec.transaction_id;
468: l_rec.column_name := 'SECONDARY_QUANTITY';
469: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
470: l_rec.error_message_name := 'IC_DEVIATION_LO_ERR';
471:
472: fnd_message.set_name('GMI', l_rec.error_message_name);
473: l_rec.error_message := FND_MESSAGE.get;
499: v_item_um,
500: v_item_um2,
501: l_secondary_quantity) ;
502:
503: UPDATE po_requisitions_interface
504: SET secondary_quantity = l_secondary_quantity
505: WHERE rowid = cr_rec.rowid;
506:
507: END IF; /*v_dualum_ind in (2,3) */
513:
514: l_rec.interface_type := 'REQIMPORT';
515: l_rec.interface_transaction_id := cr_rec.transaction_id;
516: l_rec.column_name := 'PREFFERED_GRADE';
517: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
518: l_rec.error_message_name := 'GML_NO_OPM_PREFERRED_GRADE';
519:
520: fnd_message.set_name('GML', l_rec.error_message_name);
521: l_rec.error_message := FND_MESSAGE.get;
551: WHEN NO_DATA_FOUND THEN
552: l_rec.interface_type := 'REQIMPORT';
553: l_rec.interface_transaction_id := cr_rec.transaction_id;
554: l_rec.column_name := 'PREFFERED_GRADE';
555: l_rec.table_name := 'PO_REQUISITIONS_INTERFACE';
556: l_rec.error_message_name := 'GML_NO_OPM_PREFERRED_GRADE';
557:
558: fnd_message.set_name('GML', l_rec.error_message_name);
559: l_rec.error_message := FND_MESSAGE.get;
578:
579: --Since item is not dual um controlled update all secondary attributes to NULL
580: IF v_dualum_ind = 0 THEN
581:
582: UPDATE po_requisitions_interface
583: SET secondary_quantity = NULL,
584: secondary_uom_code = NULL,
585: secondary_unit_of_measure = NULL
586: WHERE rowid = cr_rec.rowid;
589: ELSE /*either item is discrete or destination organization is discrete */
590:
591: --since item is discrete or destination organization is discrete
592: --update all process attributes to NULL.
593: UPDATE po_requisitions_interface
594: SET secondary_quantity = NULL,
595: secondary_uom_code = NULL,
596: secondary_unit_of_measure = NULL,
597: preferred_grade = NULL
601:
602: ELSE /*cr_rec.item_id IS NULL */
603:
604: --since its a one time item update all process attributes to NULL.
605: UPDATE po_requisitions_interface
606: SET secondary_quantity = NULL,
607: secondary_uom_code = NULL,
608: secondary_unit_of_measure = NULL,
609: preferred_grade = NULL