DBA Data[Home] [Help]

APPS.FLM_KANBAN_PURGE dependencies on DBMS_SQL

Line 356: l_sql_p := dbms_sql.open_cursor;

352: (i.e) If there are 2 pull sequences which form a chain because the
353: source-subinventory is the supply sub-inventory for the next then
354: only the last in chain will be picked up in this cursor */
355:
356: l_sql_p := dbms_sql.open_cursor;
357: l_sql_stmt :=
358: 'SELECT pull_sequence_id, inventory_item_id,'||
359: 'subinventory_name, locator_id'||
360: ' FROM mtl_kanban_pull_sequences MKP1'||

Line 393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );

389: ' and mkp2.subinventory_name <= nvl(:sub_to,mkp2.subinventory_name) '||
390: ' and nvl(mkp2.locator_id,-1) = nvl(mkp1.source_locator_id,-1)) '||
391: ' ORDER by mkp1.inventory_item_id ';
392:
393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );

Line 395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);

391: ' ORDER by mkp1.inventory_item_id ';
392:
393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:

Line 396: dbms_sql.define_column(l_sql_p,2,ld_item_id);

392:
393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);

Line 397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );

393: dbms_sql.parse( l_sql_p, l_sql_stmt, dbms_sql.native );
394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);

Line 398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);

394:
395: dbms_sql.define_column(l_sql_p,1,ld_pull_seq_id);
396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);

Line 400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);

396: dbms_sql.define_column(l_sql_p,2,ld_item_id);
397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);

Line 401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);

397: dbms_sql.define_column(l_sql_p,3,ld_subinv,10 );
398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);

Line 402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);

398: dbms_sql.define_column(l_sql_p,4,ld_loc_id);
399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);

Line 403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);

399:
400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);

Line 404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);

400: dbms_sql.bind_variable(l_sql_p,'org_id', arg_org_id);
401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:

Line 405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);

401: dbms_sql.bind_variable(l_sql_p,'sub_from', arg_subinv_from);
402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);

Line 406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);

402: dbms_sql.bind_variable(l_sql_p,'sub_to', arg_subinv_to);
403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP

Line 407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);

403: dbms_sql.bind_variable(l_sql_p,'source', arg_source_type);
404: dbms_sql.bind_variable(l_sql_p,'supplier_id', arg_supplier_id);
405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then

Line 409: l_sql_rows := dbms_sql.execute(l_sql_p);

405: dbms_sql.bind_variable(l_sql_p,'source_org_id', arg_source_org_id);
406: dbms_sql.bind_variable(l_sql_p,'source_subinv', arg_source_subinv);
407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);

Line 411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then

407: dbms_sql.bind_variable(l_sql_p,'line_id', arg_line_id);
408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);

Line 412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);

408:
409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416:

Line 413: dbms_sql.column_value(l_sql_p,2,ld_item_id);

409: l_sql_rows := dbms_sql.execute(l_sql_p);
410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416:
417: FOR Purge_Rec in Cur_Kanban(arg_source_type,ld_item_id,ld_subinv) LOOP

Line 414: dbms_sql.column_value(l_sql_p,3,ld_subinv);

410: LOOP
411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416:
417: FOR Purge_Rec in Cur_Kanban(arg_source_type,ld_item_id,ld_subinv) LOOP
418:

Line 415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);

411: if ( dbms_sql.fetch_rows(l_sql_p) > 0 ) then
412: dbms_sql.column_value(l_sql_p,1,ld_pull_seq_id);
413: dbms_sql.column_value(l_sql_p,2,ld_item_id);
414: dbms_sql.column_value(l_sql_p,3,ld_subinv);
415: dbms_sql.column_value(l_sql_p,4,ld_loc_id);
416:
417: FOR Purge_Rec in Cur_Kanban(arg_source_type,ld_item_id,ld_subinv) LOOP
418:
419: Purge_Kanban_Cards(purge_rec.pull_sequence_id,

Line 456: dbms_sql.close_cursor(l_sql_p);

452: END LOOP;
453:
454: else
455: -- No more rows in the cursor
456: dbms_sql.close_cursor(l_sql_p);
457: EXIT;
458: end if;
459: END LOOP;
460:

Line 472: dbms_sql.close_cursor(l_sql_p);

468: retcode := G_ERROR;
469: if (errbuf = NULL) then
470: errbuf := SUBSTR(SQLERRM, 1, 500);
471: end if;
472: dbms_sql.close_cursor(l_sql_p);
473: ROLLBACK;
474: MRP_UTIL.MRP_LOG('Error at '||l_stmt_num|| ' in Purge_Kanban_Card');
475: End Purge_Kanban;
476: END FLM_KANBAN_PURGE;