Oracle EBS OPM update material txn

Wesley13
• 阅读 889
--update_material_txn
--created by jenrry 
DECLARE
   p_mmti_rec           mtl_transactions_interface%ROWTYPE;
   p_mmli_tbl           gme_common_pvt.mtl_trans_lots_inter_tbl;
   x_return_status      VARCHAR2 (2000);
   p_validation_level   NUMBER;
   p_init_msg_list      VARCHAR2 (2000);
   p_commit             VARCHAR2 (2000);
   x_message_count      NUMBER;
   x_message_list       VARCHAR2 (2000);
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2 (2000);
   x_mmt_rec            mtl_material_transactions%ROWTYPE;
   x_mmln_tbl           gme_common_pvt.mtl_trans_lots_num_tbl;
   l_txn_count          NUMBER;
   l_count_t            NUMBER;
   l_count_i            NUMBER;
   setup_failure        EXCEPTION;
   p_org_code           VARCHAR2 (3)                            := 'PR1';
   g_debug              VARCHAR2 (5)     := fnd_profile.VALUE ('AFLOG_LEVEL'); 
   l_user_name          VARCHAR2 (80);
   l_user_id            NUMBER;

   CURSOR get_user_id (v_user_name IN VARCHAR2)
   IS
      SELECT user_id
        FROM fnd_user
       WHERE user_name = v_user_name;

   PROCEDURE display_messages (p_msg_count IN NUMBER)
   IS
      MESSAGE               VARCHAR2 (2000);
      dummy                 NUMBER;
      l_api_name   CONSTANT VARCHAR2 (30)   := 'DISPLAY_MESSAGES';
   BEGIN
      FOR i IN 1 .. p_msg_count
      LOOP
         fnd_msg_pub.get (p_msg_index          => i,
                          p_data               => MESSAGE,
                          p_encoded            => 'F',
                          p_msg_index_out      => dummy
                         );
         DBMS_OUTPUT.put_line ('Message ' || TO_CHAR (i) || ' ' || MESSAGE);
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_msg_pub.add_exc_msg ('wrapper for Update_Material_Txn', l_api_name);
   END display_messages;
BEGIN 
   DBMS_OUTPUT.ENABLE (20000); 
   l_user_name := 'PROCESS_OPS';

   OPEN get_user_id (l_user_name);

   FETCH get_user_id
    INTO l_user_id;

   IF get_user_id%NOTFOUND
   THEN
      DBMS_OUTPUT.put_line ('Invalid User ' || l_user_name);

      CLOSE get_user_id;

      RAISE NO_DATA_FOUND;
   END IF;

   CLOSE get_user_id;

   fnd_profile.initialize (l_user_id); 
   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => NULL,
                               resp_appl_id      => NULL
                              ); 
   x_return_status := fnd_api.g_ret_sts_success; 
   fnd_msg_pub.initialize;
   gme_common_pvt.g_error_count := 0;
   gme_common_pvt.set_timestamp;
   gme_common_pvt.g_move_to_temp := fnd_api.g_false;
   p_mmti_rec.source_code := 'OPM';
   p_mmti_rec.source_header_id := 19492;
   p_mmti_rec.transaction_source_id := 186706;
   p_mmti_rec.trx_source_line_id := 19492;
--p_mmti_rec.last_updated_by              :=  gme_common_pvt.g_user_ident;
--p_mmti_rec.last_update_login            :=  gme_common_pvt.g_user_ident ;
--p_mmti_rec.last_update_date             :=  gme_common_pvt.g_timestamp ;
--p_mmti_rec.creation_date                :=  gme_common_pvt.g_timestamp ;
--p_mmti_rec.created_by                   :=  gme_common_pvt.g_user_ident;
   p_mmti_rec.inventory_item_id := 178859;
   p_mmti_rec.revision := NULL;
   p_mmti_rec.organization_id := 1381;
   p_mmti_rec.transaction_date := SYSDATE;
   p_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
   p_mmti_rec.transaction_action_id := gme_common_pvt.g_ing_issue_txn_action;
   p_mmti_rec.transaction_quantity := -8;
--p_mmti_rec.primary_quantity           :=
   p_mmti_rec.transaction_uom := 'LB';
   p_mmti_rec.subinventory_code := 'PR0';
   p_mmti_rec.locator_id := 1355;
   p_mmti_rec.transaction_source_type_id := 5;
--p_mmti_rec.transaction_source_name    :=
   p_mmti_rec.wip_entity_type := 10;
--p_mmti_rec.reason_id                  :=

   /* p_mmli_tbl(1).  last_update_date       := gme_common_pvt.g_timestamp       ;
      p_mmli_tbl(1).  last_updated_by        := gme_common_pvt.g_user_ident      ;
      p_mmli_tbl(1).  creation_date          := gme_common_pvt.g_timestamp        ;
      p_mmli_tbl(1).  created_by             := gme_common_pvt.g_user_ident       ;
      p_mmli_tbl(1).  lot_number             := 'TCS'             ;
      p_mmli_tbl(1).  transaction_quantity   :=    ; */
   gme_api_pub.update_material_txn
                           (p_api_version              => 2.0,
                            p_validation_level         => gme_common_pvt.g_max_errors,
                            p_init_msg_list            => fnd_api.g_false,
                            p_commit                   => fnd_api.g_true,
                            x_message_count            => x_message_count,
                            x_message_list             => x_message_list,
                            x_return_status            => x_return_status,
                            p_transaction_id           => 12137851,
                            p_mmti_rec                 => p_mmti_rec,
                            p_mmli_tbl                 => p_mmli_tbl,
                            p_create_lot               => NULL,
                            p_generate_lot             => NULL,
                            p_generate_parent_lot      => NULL,
                            x_mmt_rec                  => x_mmt_rec,
                            x_mmln_tbl                 => x_mmln_tbl
                           );
   DBMS_OUTPUT.put_line (   'msg_count from process trxns='
                         || TO_CHAR (l_msg_count)
                        );

   IF l_msg_count > 1
   THEN
      display_messages (l_msg_count);
   END IF;

   DBMS_OUTPUT.put_line (   'after process transactions x_return_status='
                         || TO_CHAR (x_return_status)
                        );
-- Output the results
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.transaction_id = '
                                 || TO_CHAR (x_mmt_rec.transaction_id),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.transaction_type_id = '
                                 || TO_CHAR (x_mmt_rec.transaction_type_id),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.transaction_action_id = '
                                 || TO_CHAR (x_mmt_rec.transaction_action_id),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.transaction_source_id = '
                                 || TO_CHAR (x_mmt_rec.transaction_source_id),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.trx_source_line_id = '
                                 || TO_CHAR (x_mmt_rec.trx_source_line_id),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.source_line_id = '
                                 || TO_CHAR (x_mmt_rec.source_line_id),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.transaction_quantity = '
                                 || TO_CHAR (x_mmt_rec.transaction_quantity),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.transaction_uom = '
                                 || TO_CHAR (x_mmt_rec.transaction_uom),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.transaction_date = '
                                 || TO_CHAR (x_mmt_rec.transaction_date),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmln_tbl.count = '
                                 || TO_CHAR (x_mmln_tbl.COUNT),
                                 1,
                                 255
                                )
                        );

   IF (x_mmln_tbl.COUNT > 0)
   THEN
      FOR i IN 1 .. x_mmln_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line (SUBSTR (   'x_mmln_tbl((i).lot_number = '
                                       || TO_CHAR (x_mmln_tbl.COUNT),
                                       1,
                                       255
                                      )
                              );
      END LOOP;
   END IF;

   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.LAST_UPDATE_DATE = '
                                 || TO_CHAR (x_mmt_rec.last_update_date),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.LAST_UPDATED_BY = '
                                 || TO_CHAR (x_mmt_rec.last_updated_by),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.CREATION_DATE = '
                                 || TO_CHAR (x_mmt_rec.creation_date),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.CREATED_BY = '
                                 || TO_CHAR (x_mmt_rec.created_by),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR (   'x_mmt_rec.LAST_UPDATE_LOGIN = '
                                 || TO_CHAR (x_mmt_rec.last_update_login),
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
                                 1,
                                 255
                                )
                        );
   DBMS_OUTPUT.put_line ('x_message_count = ' || TO_CHAR (x_message_count));
   DBMS_OUTPUT.put_line (SUBSTR ('x_message_list = ' || x_message_list, 1,
                                 255)
                        );
   gme_common_pvt.count_and_get (x_count        => x_message_count,
                                 p_encoded      => fnd_api.g_false,
                                 x_data         => x_message_list
                                );

   IF x_message_count > 1
   THEN
      display_messages (x_message_count);
   END IF;
EXCEPTION
   WHEN setup_failure
   THEN
      DBMS_OUTPUT.put_line (' in setup failure');
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SUBSTR (   'Error '
                                    || TO_CHAR (SQLCODE)
                                    || ': '
                                    || SQLERRM,
                                    1,
                                    255
                                   )
                           );
      RAISE;
END;
/
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Easter79 Easter79
3年前
swap空间的增减方法
(1)增大swap空间去激活swap交换区:swapoff v /dev/vg00/lvswap扩展交换lv:lvextend L 10G /dev/vg00/lvswap重新生成swap交换区:mkswap /dev/vg00/lvswap激活新生成的交换区:swapon v /dev/vg00/lvswap
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
Java获得今日零时零分零秒的时间(Date型)
publicDatezeroTime()throwsParseException{    DatetimenewDate();    SimpleDateFormatsimpnewSimpleDateFormat("yyyyMMdd00:00:00");    SimpleDateFormatsimp2newS
Wesley13 Wesley13
3年前
Java爬虫之JSoup使用教程
title:Java爬虫之JSoup使用教程date:201812248:00:000800update:201812248:00:000800author:mecover:https://imgblog.csdnimg.cn/20181224144920712(https://www.oschin
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这