If this action is missing from your service role, then CodePipeline does not have permissions to run This is not an issue with the folder or artifacts. Its strength is more than 2000 people in India. enabled in your browser" or "An organization owner must install the GitHub app", Add permissions to the CodePipeline Can we load image using sqlloader 9. Your application Docker image is rebuilt and pushed to the GitLab Container Registry. In this step, you are going to edit your role trust relationship to limit only tokens with sub claim that matches a repository to assume the role. Also, we will show how to configure it to automate the deployments to Kubernetes. Chennai - 600089. Hence giving way to an enormous scope for Job seekers. Whether you have no files or many, you'll want to create a repository. And regarding the course agenda, they are very sincere and punctual. Problem: If the Jenkins server is installed on an Amazon EC2 Otherwise, the option to install the app will not display. sufficient permissions for AWS Elastic Beanstalk, including, but not limited to, some operations in Elastic Load Balancing. credentials through the profile configured on the server. ------------------------------------------------------------------------------------------------------------------------------------------- */. 11.exceptions used in ur project? to AWS. It can take up to two weeks to process requests for a quota increase. 4th Floor, W122, 3rd Ave,
1. downloaded to the build environment. Hi CandidatesHope you are doing well! On the jobs page, click, Upon successful build, your lambda function has the latest. With heap summary you can get to the bottom of what is using up your memory. Verify Recent CR in project4. Amazon CodeGuru is a developer tool that provides intelligent recommendations to improve code quality and identify an applications most expensive lines of code. Example: Change your branch name Job Location: Hyderabad/Secunderabad, Pune. Chennai 600042. what is itil process14. Team cooperation &commitment towards the work is required. You can then select Pipelines to check pipeline progress and verify that the application was successfully deployed. Use the same variable names as above. Then you can run package and procedure difference12. Azure Functions Deploy - Deploy Azure function code. The following configuration does that after replacing all variables with your configuration: The following step shows you how to update your trust policy to allow only deployment steps to assume the role. use of Bulk Collect using For All5. service role, AWS Elastic Beanstalk Use this URL for a, Specify the region where you want to have this lambda. Trained on decades of knowledge and experience, CodeGuru Reviewer uses machine learning and automated reasoning, AWS and security best practices, and hard-learned lessons across millions of code reviews on thousands of open-source and Amazon repositories to automate code reviews. Include the following details. Various skills and tech stack which are being mostly used in the environment are as follows. organization, the organization owner must create the connection to the GitHub repository. Integrate CodeGuru Reviewer and Profiler to your development pipeline to improve code quality and optimize performance for applications. Design your own course content based on your project requirements. tell about ur self2. policy. For obtaining access/bearer tokens, we support three of RFC-6749's grant flows, plus a custom Bitbucket flow for exchanging JWT tokens for access tokens. Learn AWS from beginner basics to advanced techniques with Greens Technologies Best AWS training institute in Chennai taught by experts. repositories was added on April 18, 2016. Edit the policy statement for your service role as detailed in Add permissions to the CodePipeline and the subnet/subnets separated by a comma. WebYou can use Bitbucket Pipelines with Microsoft Azure in a variety of ways using pipes.. We have the following Azure pipes: Azure ARM deploy - Deploy resources to Azure using Azure Resource Manager templates. Job Description Devops/Containerization engineer (docker + Kubernetes) DevSecOps Engg team is responsible for implementing and maintaining systems that supports the needs of continuous development, integration and deploymentsExperience on a variety stacks that includes both Linux and Windows systems Extremely proficient with Bash or other shell variants Proficient with additional languages (i.e. If the list does not include the 1.Tell about yourself2.Difference between views & materialised view?3.What is sequence and what about CYCLE,CACHE?4.What is synonym?5.what is public and private synonym?6.Difference between Function & Procedure?7.In that function DML operation and commit? If you are using an IAM user on a Jenkins server, an The legacy Deploy HTTP APIs to AWS API Gateway (v2) integration has been deprecated. Resource field, as shown in this example: Choose Review policy. Knowledge of Cloud networking, Security & Linux7. The following example shows the same bucket policy statement after the permission 3.diff Between primary and unique?4.table normalisation ?5.tell about oracle forms and reports?6.triggers used in ur current project?7.tell abt mutating table ?8.applications errors?9.can we use commit and rollback in log table?10.if yes what will happen? is getting S3 access denied for the S3 bucket , Pipelines with an Amazon S3, Amazon ECR, or CodeCommit Please find below the details required:Company Link: https://www.amtexsystems.in/Amtex SystemsAmtex System Inc. is a New York State certified MBE providing specialized technology platforms and services in Software Development & Consulting, Digital Media, Business Intelligence, Mobile Applications and Content Management.www.amtexsystems.inCloud Engineer Requirements:Bachelors Degree in Computer Science or Engineering; Masters degree a plus.Certification as an AWS Solutions Architect, Cloud Security Certification, and/or OpenStack Administrator Certification a plus. Learn everything you need to know about how to build third-party apps with Bitbucket Cloud REST API, as well as how to use OAuth. Note that Resource Owner Password !Thanks to the whole team of Greens Technolgies. Ltd.Mob : 7906649595kamender.singh@acsicorp.com. instance, the instance might not have been created with an instance role that has the Then we need a barebone test-driven development (TDD) Python project as our code base. !Greetings from Neerinfo Solutions Pvt Ltd.We have an exciting opportunity for you In TCS ( Tata Consultany Services )Staffing-PermanentExp : 5 to 10 YrsLocation: Hyderabad / PuneJob Description -Role - Technical ArchitectSkill - Amazon Web Service(AWS) Cloud ComputingMust-Have:Experience on Service Mesh (Istio/Linkerd) for EKSCustom build soln to orchestrate/choreograph Microservices, enabling gRPC, and setting up Event-driven architecture with event hubs.Deep understanding of Cloud Native and Microservices fundamentalsDeep knowledge and understanding of AWS PaaS and IaaS featuresHands on experience in AWS services i.e. Jenkins server is secured and uses HTTPS instead of HTTP. You might want to confirm that the AWS service you intend to use is. Add the Provider URL, that is displayed as an identity provider on OpenID Connect in Bitbucket, to the corresponding text field. policy statement for your service role as detailed in Add permissions to the CodePipeline Enter one or more items separated by commas. the names appear to be truncated. Roles and responsibilities3. Though I had taken online program ,the team ensured that I completed my certification successfully and got placed . u know about data migration5. Parameterized cursor11. You add the connection ARN to your CodeBuild service role Even though the artifact name appears to be truncated, CodePipeline maps to the artifact bucket For the policy attached to your CodePipeline service role, add s3:ListBucket to Note: If you dont see a Manage Jenkins Option, You need to consult with your Administrator. You might have to provide the deployed. Integrate CodeGuru into your existing software development workflow to automate code reviews during application development and continuously monitor application's performance in production and provide recommendations and visual clues on how to improve code quality, application performance, and reduce overall cost. This part is very important for crafting strict policies per environment. Special Thanks To trainer and Greens Technologies Team for helping me to get job. The staffs have real time experience which help us to learn alot apart from syllabus and we get to know about the issues and solutions. Our trainers use comprehensive set of online tools and techniques which improves online training experience.Students are requested to log in at the scheduled time by our instructor. Includes a LIVE project to deploy applications on Cloud. Get started with branches and pull requests, Control access to private content in a workspace, Transfer repositories and groups to a workspace, Import or convert code from an existing tool, Import a repository from GitHub or GitLab, Manage large files with Git Large File Storage (LFS), Use Git LFS with existing Bitbucket repositories, Current limitations for Git LFS with Bitbucket, Storage policy for Git LFS with Bitbucket, Set repository privacy and forking options, Grant repository access to users and groups, Managing multiple Repository Access keys on one device, Resolve issues automatically when users push code, Set email preferences for an issue tracker, Use Pipelines in different software languages, Javascript (Node.js) with Bitbucket Pipelines, Deploy build artifacts to Bitbucket Downloads, Build and push a Docker image to a container registry, Use glob patterns on the Pipelines yaml file, Run Docker commands in Bitbucket Pipelines, Specify dependencies in your Pipelines build, Use AWS ECR images in Pipelines with OpenID Connect, Deploy on AWS using Bitbucket Pipelines OpenID Connect, Integrate Pipelines with resource servers using OIDC, Cross-platform testing in Bitbucket Pipelines, Configure your runner in bitbucket-pipelines.yml, Use your Docker images in self-hosted runners, Deploying the Docker-based runner on Kubernetes, Managing multiple Bitbucket user SSH keys on one device, Add an App password to Sourcetree or another application, Manage email notifications for watched objects. Serverless Computing is a disruptive change in the way we used to approach the infrastructure and host our applications. cursor and ref cursor difference9. update emp table a to b and b to a6. WebAWS Lambda API. Progma Types13. It guides an aspirant to learn the core concepts, upcoming trends, and best practices for working with the AWS architecture .It ensures that an individual becomes ready and can take up any project work provided by MNCs. Python, JavaScript, Perl, Java, Powershell) Proficient with development tools (Git, Jenkins, Maven, Artifactory, SonarQube, etc) Knowledgeable with database systems and SQL (Postgres, MySQL, Oracle) Knowledgeable with working with APIs and coding solutions to leverage them Working on impromptu projects to assist with development, monitoring and/or measuring. Evaluating new and replacement systems for development teams Providing 2nd and 3rd level support to application teams Working remotely and off hours when needed, Note - If interested kindly share your cv on reema.kashyap@artechinfo.in, Job Synopsis AWS Cloud Technical LeadCompany: Locuz Enterprise Solutions ltdExperience: 5 to 9 yrsLocation: Hyderabad / Secunderabad Job Description Good knowledge of AWS CloudServerless computing architectureAWS Lambda compute services & ScriptingFull cycle AWS implementation experienceproject experience in development and maintenance support for AWS web service and Cloud-based implementationsExperience leading teams of up to 10 + professionalsAbility to manage multiple tasks and projects in a fast-moving environmentRole: Technical ArchitectRole Category: Programming & DesignIndustry Type: IT-Hardware & NetworkingFunctional Area: IT Software - Application Programming, MaintenanceKeywords: aws,devops,aws lambda,AWS SERVERLESS,SERVERLESS COMPUTING ARCHITECTURE,LAMBDA,SHELL SCRIPTING,AWS TECHNICAL LEAD,terraform, Experience required for the Job: 5 - 15 yearsAnnual Salary of the Job: 0.00 - 16.00 LacsJob Location: Bangalore/Bengaluru, Hi,We have opportunity with one of our esteemed Client for Contract-To-Hire Position for Hyderabad LocationSkill : SRE EngineerWork Location : HyderabadNotice Period : Immediate JoinersYears of experience : Above 5 yearsWork location - HyderabadWork Timing - Shift Engagment ( Morning(6:30 AM- 4:00 PM), Afternoon(11:00 AM- 8:30 PM), Evening(9:00 PM 5:30 AM)JD:1.SRE: Strong sense of ownership, Task management , customer service, and integrity demonstrated through clear communication.Proven ability to write programs using a high-level programming language like: Python, Java, Shell Scripts, Jenkins Pipeline.Proclivity towards efficient programming emphasizing improvement via complexity analysis.Experience handling large numbers of diverse systems with configuration management systems like: Jenkins, Ansible. To deploy your application with aws-eks-kubectl-run pipe youll need to have: An IAM user is configured with sufficient permissions to allow the pipe to perform a deployment to your application and upload updates to the AWS EKS cluster. Get advisories and other resources for Bitbucket Cloud. Person should have 3 -6 years of experience working in any one the database like Oracle/MySQL/SQL Must have experience in Query tuning/Performance TuningMust have worked on Linux DB migration is required Soft Skill : Good to have worked on Banking projects Should be a good team player who can coordinate and work closely with front-end developers. For problems setting up or using this feature (depending on your GitLab SQL*loader and used for? what ticket tracking tool u r used10. 2022, Amazon Web Services, Inc. or its affiliates. In the Configure provider section, select OpenID Connect. There is a 100-character limit to Access AWS Identity and Access Management (IAM). Chennai-600 020. Add a settings.xml file to your source code.. 12. Existing legacy integrations will continue to work, but you should start using the new AWS API Gateway integration as soon as D Package created to define Air Quotation. 4. how to genrate forms having particular duration4. AWS certified solutions architect certification program helps an individual to qualify for a position as a top class and efficient AWS professional. Well use Bitbucket Pipelines to build and push a docker image to a container registry (Docker Hub). 1. CodeGuru Reviewer analyzes existing code bases in the repository, identifies hard-to-find bugs and critical issues with high accuracy, provides intelligent suggestions on how to remediate them, and creates a baseline for successive code reviews. returns an error message: "Deployment failed. Jenkins Tomcat Deploy We are going to see how to pull the code from the Source Code Management Repository - GITHUB and deploy it to, AWS CLI has become a life saver when you want to manage your AWS infrastructure efficiently. Projects makes it easier for members of a workspaceto collaborate by organizing your repositories into projects. what is latest version of oracle DB used8. I was working with a leading MNC and I had to leave my job as I couldnt upgrade myself. Make sure that you save it as a secured variable. Learn how to manage your plans and billing, update settings, and configure SSH and two-step verification. 05,
Brief about yourself2.Roles and Responsibilities 3. CodeDeployToECS deploy action is 3 MB. Experience with Infra/Deployment architecture and CI&CD processes using Jenkins, Shell scripts, SonarQube, GIT, Cloud Formation, Cloud Watch and Ansible.4. Overall 5+ Years of Experience in IT.2. You can configure it to run on a pull, push, or scheduled run of your pipeline and view your code quality and security recommendations within the AWS Console or within your CI/CD providers user interface. Subscribe to our channel Jenkins Tomcat Deploy - Deploying Application to Tomcat using Jenkins, AWS S3 CP Examples - How to Copy Files with S3 CLI | Devops Junction, Find Who owns the Private IP in AWS | DevOps Junction, AWS change instance type - From Console or AWS CLI, Some Basic Skills on Python ( just to understand our HelloWorld app). But Jenkins are still many peoples favourite and I could be somewhere on the top order of that fans list I presume. Opp to DLF IT Park,
In the install phase of your build project, instruct CodeBuild to copy your Egattur Village,
20 30. Possible fixes: The list of repositories provided in the You may need to search for the policy, if you do not see it on the current list. Possible fixes: For repositories in a GitHub Get started building with Amazon CodeGuru in the AWS Management Console. The following step shows you how to update your trust policy to allow only a specific repository to assume the role. Notice period. To find the connection ARN for your pipeline, open your pipeline and click the (i) The AWS/Deploy-ECS template ships with GitLab and is available Name of the function (Note: If the function is not yet created, this job will automatically create and deploy). 10 20. the input artifact can be passed to the build: The default: The source action produces a zip file that contains the code that CodeBuild How we are going to deploy AWS Lambda Functions with Jenkins. WebCodeGuru Reviewer Security Detector leverages automated reasoning and AWSs years of security experience to improve your code security. GitLab provides Docker images with the libraries and tools you need to deploy Thanks & RegardsChakshu Madanchakshu@amtexsystems.com, Urgent Hiring|| AWS Engineer|| BangaloreExperience required for the Job: 6 - 10 yearsAnnual Salary of the Job: 13.00 - 14.00 LacsJob Location: Bangalore/BengaluruKeyskills : aws,cloud,devops,aws devops,aws cloud,scripting,aws certification,linux,docker,puppet,ansible,Cloudformation,Terraforms, We are hiring AWS Engineers , for Bangalore About the company : 30 Yr old IT infrastructure managed space services organisationWorking Location :Ecospace / Bagmane Tech ParkCandidate should be ok with Australian Shift timingsNotice Period : Immediate to 30 Days onlyPermanent Position, Job DescriptionTechnical Skills - Mandatory ExpertiseExperience AWS Cloud Platform Good DevOps Experience IAC using Cloudformation, Terraform CI/CD experience with Gitlab Linux Experience in BASH /Python scripting Good to have AWS Certifications, Technical Skills - KnowledgeKnowledge in AWS Security and Networking Services Experience working in Agile Team Knowledge of Database technologies on AWS Experience with configuration management tools, e.g. Sanatorium,
To add permissions to your CodeBuild service role policy, you create a customer-managed policy || FS-19082020-01 19-AUG-2020 Harish Kumar.D Added getLov_customerdetails_prc. The DevOps engineer willhave intimate knowledge of AWS infrastructure including but not limited to IAM, DNS, S3, and CDNs.This role requires on-demand support for all mission critical support. The following error message is returned the codecommit:GitPull permission in the action field. 8: how many members are there in your team? Develop Cloud-native applications and Get Cloud access of each AWS Online training session that you attend through GotoMeeting. how will you view changes happening in log file live in unix33. Set up and work on repositories in Bitbucket Cloud. connection-permissions), and then choose Create source no longer start automatically, Connections error when connecting to GitHub: "A problem occurred, make sure cookies are or create a rule for the new source settings. CodeCommit action, see CodeCommit source actions and CloudWatch Events. 3: tell about SLA's and responce and resolution timing? If you want these JSON objects saved in your repository, save the objects as three Location- Mumbai Experience :6 to 9 yrs Mandatory Skills :AWS DevOpsNotice period: Require immediate joinee- 15 daysDesired Skills :Experience of working on Jenins, Docker, Kunbernetes and AWS servicesPrior exp of GEExp of working in Agile project". missing, Pipeline error: A source action that you attach to your CodeBuild service role. 2. To use GitLab CI/CD to connect to AWS, you must authenticate. codecommit-gitpull), and then choose Create The constant needfor great documentation will be a pivotal part of this role.Experience: Technical background with at least 5 years of DevOps experience preferred 2 years of Kubernetes Cluster management experience College or Graduate level degree is preferredCulture: Polite and respectful in working with team members Initiative in taking on additional tasks and responsibilities Responsive and positive attitude in a fast paced environment Unafraid to ask questions or follow upProficiencies and Requirements: Excellent written and oral communication skills in English Ability to decipher technical challenges and outline technical tasks Ability to reduce, summarize and report issues to management Working knowledge of Linux systems, networking, and administration Working git and git flow Cloud Infrastructure systems, AWS Container systems, such as Docker and container registries Experience with monitoring and alerting systems (sentry.io, grafana, etc..) Database systems with replication, sharding, and clustering Fail-over and disaster recovery planning experience Excellent documentation and information dissemination skills, Send your CV to nagesh.hripple@gmail.comMention following details:-current CTCExpected CTCNotice period, Nagesh GuralwarSr.HR Consultantnagesh.hripple@gmail.com, Devops AWS for Mumbai with one of the healthcare orgExperience required for the Job: 6 - 9 yearsJob Location: Mumbai. Specify the function time out between 1 300 seconds. Padmanabha Nagar,
Can we manipulate data in view4. aws commands in your CI/CD jobs. What's view2. Commit and push your updated .gitlab-ci.yml to your projects repository. The AWS region code (us-east-1, us-west-2, etc.) for an Amazon ECR action, see Amazon ECR source actions and CloudWatch Events. After completion of graduation, was very disappointed in life as I didnt get through any campus interviews.. Then I chose Greens technologies for learning AWS as my friend conveyed that it was THE BEST Institute. Difference between procedure and function16. Type: BuildStatusConfig object. uses the IAM user configured with the correct permissions. American Advent Christian Layout, Sholinganallur to Medavakkam Main Road,
Hints9. Definitely yes. Excellent communication skills9. If the task definition is a JSON file in GitLab, the filename, including the path. Go toDashboard -> New Item (Freestyle project). Knowledge in IAM Roles and Policies8. action, see Amazon S3 source actions and CloudWatch Events. 1. Follow us on Facebook or Twitter Kindly share update resume and detailsRelevant Exp on DevOps and FullStack:C CTC:E CTC:Notice period:Current Location:Availability for Video interview: Company Profile:ACS (American CyberSystems) URL:(http://www.acsicorp.com)American CyberSystems, Inc. (ACS) is a global information technology services company offering ITand Engineering consulting, healthcare, and workforce management solutions to Fortune 1000companies across all major industries. Projects makes it easier for members of a workspaceto collaborate by organizing your repositories into projects. synonym and what is public and private16. 1. Tamil Nadu - 600040. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. This also provides lot of possibilities for Automation and reduce the number of times that you have to login to AWS Management console. Artificial Intelligence Training in Chennai, Intelligent Apps (I - Apps) Training in Chennai, Cognitive Cloud Computing Training in Chennai, Augmented Reality and Virtual Reality Classes in Chennai, Internet of Things (IoT) Classes in Chennai, The Benefits of Being Able to Learn and Study At Your Own Pace. permissions to access the repository. If interested, please revert with you updated CV only in "word format" along with the following details asap; Bhagyashree@mitsit.in* Current CTC:* Expected CTC:* Notice period:PAN Card No :Current location (Chennai /Hometown ) :Comfortable for Payroll:* Your full name:* Marital status:-- Thanks & Regards,Bhagyashree Pashte IT - RecruitmentMITS Global Consulting Pvt.LtdPh: 91-9137875045Website: www.mitsit.in, * Person should have 3 -6 years of experience working in any one the database like Oracle/MySQL/SQL, * Must have experience in Query tuning, Shell Scripting, Performance tuning & Sql queries, * Must be ready to work from Office location whenever notified, this is from KG information system Pvt. Index4. What is Synonym5. the existing policy as shown in the following example. With Serverless Computing like AWS Lambda, you can simply deploy your single python file without having to worry about writing the production-grade wrappers like gunicorn and supervisor etc. On the JSON tab, paste the following sample policy. change-detection resources created by the console. Specify the language in which you are writing the function. The task definition file is a required artifact for the CodePipeline deploy action to Amazon ECS Web Identity Providers allow the system to receive an authentication token, and then use or exchange that token for temporary security credentials in AWS. Artifactory , Full stack development exposure in Javascript, middleware, JavaExpertise in Kafka or similar technologiesDeep understanding of the Linux Operating System, including Kernel, Memory, Process, Threads, Static / Shared Libraries, IPC, Signals.Understanding of standard networking protocols and components such as: HTTP, DNS, ECMP, TCP/IP, ICMP, the OSI Model, Subnetting and Load Balancing strategies.Familiarity with distributed systems is a plus including: Microservices.Working knowledge and understanding of Blob/object storage.Experience with Kubernetes, Prometheus, Grafana, and/or Docker.Passion for eliminating repetitive manual processes using automation.Tools : GITlab , Bamboo , Gradle , Maven , ant , ansible , splunk , Confluence , JIRA.Apple technologies : Apple Connect integration , Slack , Quip , Radar , CST. request is created for the organization owner, and one of the following errors display: A problem occurred, make sure cookies are enabled in your browser, An organization owner must install the GitHub app. Manage your plans and settings in Bitbucket Cloud. If you are thinking about finding a job in AWS then it must be a great choice. error, Pipeline artifact folder names appear to Web Identity Providers allow the system to receive an authentication token, and then use or exchange that token for temporary security credentials in AWS. This is not a recommended best practice. Learn end to end AWS course content that is similar to instructor led classroom training. Now let us create a new Job as a free style project. A workspace contains projects and repositories. The most prominent tool we have all been using in the last decade for Continuous Integration and Continuous Deployment is still there to save us. After you set up authentication, you can configure CI/CD to deploy. The Provider URL is the secure OpenID Connect URL used for authentication requests. Function Name:Name of the function (Note: If the function is not yet created, this job will automatically create and deploy). Company name:craft siliconName:Mary nancyPh no:+91 73581 96990Mail id:marynancy.r@craftsilicon.com, Indhu: +918778972194Object frontier software (OFS) Shushma.chellapandi@object-frontier.com, +91 81899 32190Name:SelliCompany: TCS Client : ally bankExpectation: 6 + years, Email : pavithra.gopalakrishnan@csscorp.com
Create an ECS task definition, where the value for the. Ramapuram,
All rights reserved. or CloudWatch Events) for change detection, the console might not detect a change where source trigger Implementing migration plan and validating the entire process. Jain college bus stop. Runtime:Specify the language in which you are writing the function. Note:If you dont have it ready, we got you covered. CI/CD variables. your CodePipeline service role's policy. But if freedom, autonomy, and life-affirming, head-scratching professional challenges rock your world, we could be a match made in heaven.About Techolution:Techolution is a high-tech consulting company on a mission to accelerate digital transformation for our clients across the globe. CodeGuru Profiler optimizes performance for applications running in production and identifies the most expensive lines of code. If you are using an IAM user, make sure the AWS profile configured on the instance In the Policy text field, enter a new bucket policy, or edit App passwords are designed to be used for a single purpose with limited permissions, so they don't require two-step verification (2SV, also known as two-factor authentication or 2FA). Timeout:Specify the function time out between 1 300 seconds. The deployment job finishes when the deployment to EC2 51-A, 2nd floor,
AWS Elastic Beanstalk deploy action hangs instead of failing if the "DescribeEvents" permission is IF p_fsprc_id IS NOT NULL THEN UPDATE FSRPF_Roleprocessfeaturelink SET fsrpf_lastupdatedby =p_user_id , fsrpf_lastupdatedon = SYSDATE WHERE fsrpl_id =p_fsrpl_id; DELETE FROM FSRPF_Roleprocessfeaturelink WHERE fsrpl_id=p_fsrpl_id; FSRPL_RoleProcessLink_pkg.put_fsrpl_prc( p_fsrpl_id => p_fsrpl_id , p_fsrol_id => p_fsrol_id , p_fsprc_id => p_fsprc_id , p_user_id => p_user_id , p_record_status => fs_constants.k_update_status , p_issuccess => p_issuccess , p_message => p_message); END IF; IF p_fsfrg_id IS NOT NULL THEN FSRPF_Roleprocessfeaturelink_pkg.put_fsrpf_prc ( p_fsrpf_id => p_fsrpf_id , p_fsrpl_id => p_fsrpl_id , p_fsfrg_id => p_fsfrg_id , p_fsrpf_isactive => p_fsrpf_isactive, p_user_id => p_user_id , p_record_status => fs_constants.k_insert_status, p_issuccess => p_issuccess , p_message => p_message ); END IF; p_issuccess := fs_constants.k_success; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_DUAL; ELSIF p_record_status= fs_constants.k_delete_status THEN. WebPortal zum Thema IT-Sicherheit Praxis-Tipps, Know-How und Hintergrundinformationen zu Schwachstellen, Tools, Anti-Virus, Software, Firewalls, E-Mail He kept his lessons simple and He not only taught me the subject but also incorporated loads of confidence in me.Ever Thankful to Karthik Sir and his team. The targeted task definition is updated with the location of the new In Unix $?,$$,$# for what it is used. If the application is being installed in a Bitbucket workspace, you need Administer workspace permissions. Docker image, and a new revision is created in ECS as result. Here is a simple tutorial from AWS:Creating the Amazon EKS Worker Node IAM Role. differnce in collctions, Syntax for collection.7. so we've made it first class by allowing you to manage your Composer authentication credentials (auth.json). Signup for Exclusive "Subscriber-only" Content, In this post, we are going to look at the steps required to configure the Jenkins and Tomcat and achieve Continuous Deployment and Continuous Integration. TYPE t_list IS REF CURSOR; PROCEDURE getLov_Airquotationreference_prc ( p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, -- To get air tariff reference number p_type IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE , p_searchtext IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE , p_page_index IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE, p_page_size IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE, p_sort_field IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE, p_sort_type IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE, p_Airquotation_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE, p_Airquotationreference OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsbkg_booking.fsbkg_referencenumber%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE getLov_copyreference_prc ( p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, -- To get copy reference number p_searchtext IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE , p_page_index IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE, p_page_size IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE, p_sort_field IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE, p_sort_type IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE, p_Airquotation_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE, p_Airquotationreference OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsbkg_booking.fsbkg_referencenumber%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE GetList_fsaqh_Prc ( --search for air quotation p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_fsbue_idcustomer IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE, p_fsaqh_quotationtype IN fsaqh_airquotationheader.fsaqh_quotationtype%TYPE, p_fsaqh_shipmenttype IN fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE, p_fsaqh_referencenumber IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsaqh_validfrom IN fsaqh_airquotationheader.fsaqh_validfrom%TYPE, p_fsaqh_validto IN fsaqh_airquotationheader.fsaqh_validto%TYPE, p_fsbue_idcarrier IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE, p_fsart_idfromAirport IN fsaqt_airquotationtransportstation.fsart_idfromAirport%TYPE, p_fsart_idtoAirport IN fsaqt_airquotationtransportstation.fsart_idtoAirport%TYPE, p_quotationparty IN fsaqh_airquotationheader.fsaqh_quotationtype%TYPE, p_partner IN fsaqh_airquotationheader.fsofo_id%TYPE, p_fsaqh_status IN fsaqh_airquotationheader.fsaqh_status%TYPE, p_page_index IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_page_size IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_sort_field IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE, p_sort_type IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE, p_fsaqh_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsaqh_list OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE Get_airquotationratedetails_Prc ( --Quotation creation p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_airquotationrateheader_details OUT FS_AirQuotationManagement.t_list, p_airquotationrate_details OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE Get_Ratefromtariff_Prc ( --get rates from tariff p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_fsbue_idcustomer IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE, p_fssqh_shipmenttype IN fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE, p_fsbue_idcarrier IN fsbue_businessentity.fsbue_name%TYPE, p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE, p_header_details OUT FS_AirQuotationManagement.t_list, p_Tariff_details OUT FS_AirQuotationManagement.t_list, p_weightdetails OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE Get_localcharges_Prc ( --get local charges p_fsofo_id IN fssqh_seaquotationheader.fsofo_id%TYPE, p_fsbue_idcustomer IN fssqh_seaquotationheader.fsbue_idcustomer%TYPE, p_fsofo_idpartner IN fssqh_seaquotationheader.fsofo_idpartner%TYPE, p_fssqh_shipmenttype IN fssqh_seaquotationheader.fssqh_shipmenttype%TYPE, p_fssqh_isexportimport IN fssqh_seaquotationheader.fssqh_isexportimport%TYPE, p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE, p_Tariff_deatils OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fssqh_seaquotationheader.fssqh_shipmenttype %TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE Get_airquotationtariff_Prc ( --Retrive quotation details p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsaqt_id IN fsaqh_airquotationheader.fsaqh_remarks%TYPE, p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_airquotationhdrdetails OUT FS_AirQuotationManagement.t_list, p_airquotationport_details OUT FS_AirQuotationManagement.t_list, p_airquotationrate_details OUT FS_AirQuotationManagement.t_list, p_surcharge_details OUT FS_AirQuotationManagement.t_list, p_localcharge_details OUT FS_AirQuotationManagement.t_list, p_weightdetails OUT FS_AirQuotationManagement.t_list, p_precarriage_details OUT FS_AirQuotationManagement.t_list, p_precarriagecharge_details OUT FS_AirQuotationManagement.t_list, p_oncarriage_details OUT FS_AirQuotationManagement.t_list, p_oncarriagecharge_details OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE Get_intermodel_Prc ( p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsaqu_id IN FSAQU_AirQuotationIntermodel.fsaqu_id%TYPE, p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_precarriage_details OUT FS_AirQuotationManagement.t_list, p_precarriagecharge_details OUT FS_AirQuotationManagement.t_list, p_oncarriage_details OUT FS_AirQuotationManagement.t_list, p_oncarriagecharge_details OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaqh_prc( p_fsaqh_id IN OUT FSAQH_AirQuotationHeader.fsaqh_id%TYPE, p_fsofo_id IN FSAQH_AirQuotationHeader.fsofo_id%TYPE, p_fsaqh_referencenumber IN OUT FSAQH_AirQuotationHeader.fsaqh_referencenumber%TYPE, p_fsaqh_referencedate IN FSAQH_AirQuotationHeader.fsaqh_referencedate%TYPE, p_fsbue_idcustomer IN FSAQH_AirQuotationHeader.fsbue_idcustomer%TYPE, p_fsofo_idsalesoffice IN FSAQH_AirQuotationHeader.fsofo_idsalesoffice%TYPE, p_fsaqh_customerdetails IN FSAQH_AirQuotationHeader.fsaqh_customerdetails%TYPE, p_fsaqh_quotationtype IN FSAQH_AirQuotationHeader.fsaqh_quotationtype%TYPE, p_fsaqh_shipmenttype IN FSAQH_AirQuotationHeader.fsaqh_shipmenttype%TYPE, p_fsaqh_minimumconfirmedquantity IN FSAQH_AirQuotationHeader.fsaqh_minimumconfirmedquantity%TYPE, p_fsaqh_minimumconfirmedquantitytype IN FSAQH_AirQuotationHeader.fsaqh_minimumconfirmedquantitytype%TYPE, p_fsaqh_incoterms IN FSAQH_AirQuotationHeader.fsaqh_incoterms%TYPE, p_fsaqh_isexportimport IN FSAQH_AirQuotationHeader.fsaqh_isexportimport%TYPE, p_fscry_idquotation IN FSAQH_AirQuotationHeader.fscry_idquotation%TYPE, p_fsaqh_validfrom IN FSAQH_AirQuotationHeader.fsaqh_validfrom%TYPE, p_fsaqh_validto IN FSAQH_AirQuotationHeader.fsaqh_validto%TYPE, p_fsaqh_weight IN FSAQH_AirQuotationHeader.fsaqh_weight%TYPE, p_fsmea_id IN FSAQH_AirQuotationHeader.fsmea_id%TYPE, p_fsaqh_volume IN FSAQH_AirQuotationHeader.fsaqh_volume%TYPE, p_fsaqh_quotationpartytype IN FSAQH_AirQuotationHeader.fsaqh_quotationpartytype%TYPE, p_fsofo_idpartner IN FSAQH_AirQuotationHeader.fsofo_idpartner%TYPE, p_fsaqh_isshowcarrier IN FSAQH_AirQuotationHeader.fsaqh_isshowcarrier%TYPE, p_fsaqh_withdrawreason IN FSAQH_AirQuotationHeader.fsaqh_withdrawreason%TYPE, p_fsaqh_status IN FSAQH_AirQuotationHeader.fsaqh_status%TYPE, p_fsaqh_remarks IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_user_id IN FSAQH_AirQuotationHeader.fsaqh_createdby%TYPE, p_record_status IN FSAQH_AirQuotationHeader.fsaqh_purgeyear%TYPE, p_issuccess OUT FSAQH_AirQuotationHeader.fsaqh_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaqc_prc( p_fsaqc_id IN OUT FSAQC_AirQuotationCarrier.fsaqc_id%TYPE, p_fsofo_id IN FSAQC_AirQuotationCarrier.fsofo_id%TYPE, p_fsaqh_id IN FSAQC_AirQuotationCarrier.fsaqh_id%TYPE, p_fsbue_idcarrier IN FSAQC_AirQuotationCarrier.fsbue_idcarrier%TYPE, p_user_id IN FSAQC_AirQuotationCarrier.fsaqc_createdby%TYPE, p_record_status IN FSAQC_AirQuotationCarrier.fsaqc_purgeyear%TYPE, p_issuccess OUT FSAQC_AirQuotationCarrier.fsaqc_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaqd_prc( p_fsaqd_id IN OUT FSAQD_AirQuotationDeal.fsaqd_id%TYPE, p_fsofo_id IN FSAQD_AirQuotationDeal.fsofo_id%TYPE, p_fsaqh_id IN FSAQD_AirQuotationDeal.fsaqh_id%TYPE, p_fsdlm_id IN FSAQD_AirQuotationDeal.fsdlm_id%TYPE, p_user_id IN FSAQD_AirQuotationDeal.fsaqd_createdby%TYPE, p_record_status IN FSAQD_AirQuotationDeal.fsaqd_purgeyear%TYPE, p_issuccess OUT FSAQD_AirQuotationDeal.fsaqd_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaqt_prc( p_fsaqt_id IN OUT FSAQT_AirQuotationTransportStation.fsaqt_id%TYPE, p_fsofo_id IN FSAQT_AirQuotationTransportStation.fsofo_id%TYPE, p_fsaqh_id IN FSAQT_AirQuotationTransportStation.fsaqh_id%TYPE, p_fsart_fromport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_fsart_toport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_transhipmentport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_fsaqt_travellingtime IN FSAQT_AirQuotationTransportStation.fsaqt_travellingtime%TYPE, p_fsaqt_dayoffrequency IN FSAQT_AirQuotationTransportStation.fsaqt_dayoffrequency%TYPE, p_routeexist IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_user_id IN FSAQT_AirQuotationTransportStation.fsaqt_createdby%TYPE, p_record_status IN FSAQT_AirQuotationTransportStation.fsaqt_purgeyear%TYPE, p_issuccess OUT FSAQT_AirQuotationTransportStation.fsaqt_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaql_prc( -- Air Quotation Email p_fsaql_id IN OUT FSAQL_AirQuotationEmail.fsaql_id%TYPE, p_fsofo_id IN FSAQL_AirQuotationEmail.fsofo_id%TYPE, p_fsaqh_id IN FSAQL_AirQuotationEmail.fsaqh_id%TYPE, p_fsaql_email IN FSAQH_AirQuotationheader.fsaqh_remarks%TYPE, p_user_id IN FSAQL_AirQuotationEmail.fsaql_createdby%TYPE, p_record_status IN FSAQL_AirQuotationEmail.fsaql_purgeyear%TYPE, p_issuccess OUT FSAQL_AirQuotationEmail.fsaql_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaqe_prc( p_fsaqe_id IN OUT FSAQE_AirQuotationEquipment.fsaqe_id%TYPE, p_fsofo_id IN FSAQE_AirQuotationEquipment.fsofo_id%TYPE, p_fsaqt_id IN FSAQE_AirQuotationEquipment.fsaqt_id%TYPE, p_fseqt_sizetypeuld IN FSAQE_AirQuotationEquipment.fseqt_sizetypeuld%TYPE, p_fsaqe_cargotype IN FSAQE_AirQuotationEquipment.fsaqe_cargotype%TYPE, p_fscmg_group IN FSAQH_AirQuotationheader.fsaqh_remarks%TYPE, p_user_id IN FSAQE_AirQuotationEquipment.fsaqe_createdby%TYPE, p_record_status IN FSAQE_AirQuotationEquipment.fsaqe_purgeyear%TYPE, p_issuccess OUT FSAQE_AirQuotationEquipment.fsaqe_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaqu_prc( p_fsaqu_id IN OUT FSAQU_AirQuotationIntermodel.fsaqu_id%TYPE, p_fsofo_id IN FSAQU_AirQuotationIntermodel.fsofo_id%TYPE, p_fsaqh_id IN FSAQU_AirQuotationIntermodel.fsaqh_id%TYPE, p_fsaqu_shipmenttype IN FSAQU_AirQuotationIntermodel.fsaqu_shipmenttype%TYPE, p_fsaqu_ismultimode IN FSAQU_AirQuotationIntermodel.fsaqu_ismultimode%TYPE, p_fsaqu_ismultistop IN FSAQU_AirQuotationIntermodel.fsaqu_ismultistop%TYPE, p_fsloc_idfrom IN FSAQU_AirQuotationIntermodel.fsloc_idfrom%TYPE, p_fsaqu_zipcodefrom IN FSAQU_AirQuotationIntermodel.fsaqu_zipcodefrom%TYPE, p_fsaqu_zipcodeto IN FSAQU_AirQuotationIntermodel.fsaqu_zipcodeto%TYPE, p_fsloc_idto IN FSAQU_AirQuotationIntermodel.fsloc_idto%TYPE, p_fsaqu_modeoftransport IN FSAQU_AirQuotationIntermodel.fsaqu_modeoftransport%TYPE, p_fsaqu_transit IN FSAQU_AirQuotationIntermodel.fsaqu_transit%TYPE, p_fsaqu_transittype IN FSAQU_AirQuotationIntermodel.fsaqu_transittype%TYPE, p_fsaqu_specialinstruction IN FSAQU_AirQuotationIntermodel.fsaqu_specialinstruction%TYPE, p_fsaqu_fromlocationaddress IN FSAQU_AirQuotationIntermodel.fsaqu_fromlocationaddress%TYPE, p_fsaqu_tolocationaddress IN FSAQU_AirQuotationIntermodel.fsaqu_tolocationaddress%TYPE, p_fsaqu_locationtype IN FSAQU_AirQuotationIntermodel.fsaqu_locationtype%TYPE, p_fsaqu_idparent IN FSAQU_AirQuotationIntermodel.fsaqu_idparent%TYPE, p_fsaqu_purgeyear IN FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE, p_user_id IN FSAQU_AirQuotationIntermodel.fsaqu_createdby%TYPE, p_record_status IN FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE, p_issuccess OUT FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaqr_prc( p_fsaqr_id IN OUT FSAQR_AirQuotationRate.fsaqr_id%TYPE, p_fsofo_id IN FSAQR_AirQuotationRate.fsofo_id%TYPE, p_fsaqt_id IN FSAQR_AirQuotationRate.fsaqt_id%TYPE, p_fsaqu_id IN FSAQR_AirQuotationRate.fsaqu_id%TYPE, p_fsatr_id IN FSAQR_AirQuotationRate.fsatr_id%TYPE, p_fsasr_id IN FSAQR_AirQuotationRate.fsasr_id%TYPE, p_fslar_id IN FSAQR_AirQuotationRate.fslar_id%TYPE, p_fschg_id IN FSAQR_AirQuotationRate.fschg_id%TYPE, p_fsaqr_basis IN FSAQR_AirQuotationRate.fsaqr_basis%TYPE, p_fsaqr_cargotype IN FSAQR_AirQuotationRate.fsaqr_cargotype%TYPE, p_fseqt_sizetypeuld IN FSAQR_AirQuotationRate.fseqt_sizetypeuld%TYPE, p_fscmg_id IN FSAQR_AirQuotationRate.fscmg_id%TYPE, p_fsaqr_weighttype IN FSAQR_AirQuotationRate.fsaqr_weighttype%TYPE, p_fscry_id IN FSAQR_AirQuotationRate.fscry_id%TYPE, p_fsaqr_ispax IN FSAQR_AirQuotationRate.fsaqr_ispax%TYPE, p_fsaqr_weight IN FSAQR_AirQuotationRate.fsaqr_weight%TYPE, p_fsmea_id IN FSAQR_AirQuotationRate.fsmea_id%TYPE, p_fsaqr_tariffrate IN FSAQR_AirQuotationRate.fsaqr_tariffrate%TYPE, p_fsaqr_markuptype IN FSAQR_AirQuotationRate.fsaqr_markuptype%TYPE, p_fsaqr_markupvalue IN FSAQR_AirQuotationRate.fsaqr_markupvalue%TYPE, p_fsaqr_sellingrate IN FSAQR_AirQuotationRate.fsaqr_sellingrate%TYPE, p_fsaqr_mintariffrate IN FSAQR_AirQuotationRate.fsaqr_mintariffrate%TYPE, p_fsaqr_minmarkuptype IN FSAQR_AirQuotationRate.fsaqr_minmarkuptype%TYPE, p_fsaqr_minmarkupvalue IN FSAQR_AirQuotationRate.fsaqr_minmarkupvalue%TYPE, p_fsaqr_minsellingrate IN FSAQR_AirQuotationRate.fsaqr_minsellingrate%TYPE, p_fsaqr_additionaldayfee IN FSAQR_AirQuotationRate.fsaqr_additionaldayfee%TYPE, p_fsaqr_pivotweight IN FSAQR_AirQuotationRate.fsaqr_pivotweight%TYPE, p_fsbue_idcarrier IN FSAQR_AirQuotationRate.fsbue_idcarrier%TYPE, p_fsaqr_isexportimport IN FSAQR_AirQuotationRate.fsaqr_isexportimport%TYPE, p_fsaqr_remarks IN FSAQR_AirQuotationRate.fsaqr_remarks%TYPE, p_inclusivecharge IN FSAQR_AirQuotationRate.fsaqr_remarks%TYPE, p_user_id IN FSAQR_AirQuotationRate.fsaqr_createdby%TYPE, p_record_status IN FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE, p_issuccess OUT FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); PROCEDURE put_fsaqw_prc( p_fsaqw_id IN OUT FSAQW_AirQuotationSlabRate.fsaqw_id%TYPE, p_fsofo_id IN FSAQW_AirQuotationSlabRate.fsofo_id%TYPE, p_fsaqr_id IN FSAQW_AirQuotationSlabRate.fsaqr_id%TYPE, p_fsatm_id IN FSAQW_AirQuotationSlabRate.fsatm_id%TYPE, p_fsaqw_buyingrate IN FSAQW_AirQuotationSlabRate.fsaqw_buyingrate%TYPE, p_fsaqw_markupvalue IN FSAQW_AirQuotationSlabRate.fsaqw_markupvalue%TYPE, p_fsaqw_sellingrate IN FSAQW_AirQuotationSlabRate.fsaqw_sellingrate%TYPE, P_fsaqw_weight IN FSAQW_AirQuotationSlabRate.fsaqw_weight%TYPE, p_user_id IN FSAQR_AirQuotationRate.fsaqr_createdby%TYPE, p_record_status IN FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE, p_issuccess OUT FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list); END FS_AirQuotationManagement; create or replace PACKAGE BODY FS_AirQuotationManagement AS PROCEDURE getLov_Airquotationreference_prc ( p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, -- To get air tariff reference number p_type IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE , p_searchtext IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE , p_page_index IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE, p_page_size IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE, p_sort_field IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE, p_sort_type IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE, p_Airquotation_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE, p_Airquotationreference OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsbkg_booking.fsbkg_referencenumber%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS l_query CLOB; l_query_cnt CLOB; BEGIN IF FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_type) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_searchtext) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_field) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_type) = 'Y' THEN l_query :='SELECT NULL AS fsaqh_id , NULL AS fsaqh_referencenumber FROM fsaqh_airquotationheader fsaqh WHEER 1=2 ' ; ELSE l_query :='SELECT fsaqh.fsaqh_id AS fsaqh_id , fsaqh.fsaqh_referencenumber AS fsaqh_referencenumber FROM fsaqh_airquotationheader fsaqh WHERE ('''|| p_type || ''' IS NULL OR fsaqh.fsaqh_quotationtype = '''|| p_type || ''') AND (('''|| p_searchtext|| ''' IS NULL) OR ( (UPPER(fsaqh.fsaqh_referencenumber) LIKE '''||'%'|| UPPER (p_searchtext)|| '%'||'''))) '; l_query_cnt := 'SELECT COUNT(fsaqh_id) FROM (' || l_query || ') '; p_Airquotation_count :=0; IF p_page_index > 0 THEN EXECUTE IMMEDIATE l_query_cnt INTO p_Airquotation_count; l_query := l_query || ' order by '; l_query := l_query || p_sort_field; l_query := l_query || ' '; l_query := l_query || p_sort_type; l_query := l_query || ' offset ( ('|| p_page_index|| ' - 1) * '|| p_page_size|| ') rows fetch next '|| p_page_size|| ' rows only '; END IF; OPEN p_Airquotationreference FOR l_query; END IF; p_issuccess := fs_constants.k_success; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_Dual; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END getLov_Airquotationreference_prc; PROCEDURE getLov_copyreference_prc ( p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, -- To get copy reference number p_searchtext IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE , p_page_index IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE, p_page_size IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE, p_sort_field IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE, p_sort_type IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE, p_Airquotation_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE, p_Airquotationreference OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsbkg_booking.fsbkg_referencenumber%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS l_query CLOB; l_query_cnt CLOB; BEGIN IF FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_searchtext) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_field) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_type) = 'Y' THEN l_query :='SELECT NULL AS fsaqh_id , NULL AS fsaqh_referencenumber, NULL AS Quotationpartytype, NULL AS fsaqh_validto, NULL AS fsgec_export_description, NULL AS Customername, NULL AS Partnername FROM fsdul_dual fsdul WHEER 1=2 ' ; ELSE l_query :='SELECT fsaqh.fsaqh_id AS fsaqh_id , fsaqh.fsaqh_referencenumber AS fsaqh_referencenumber, partytype.fsgec_description AS Quotationpartytype, fsaqh.fsaqh_validto AS fsaqh_validto, fsgec_export.fsgec_description AS fsgec_export_description, fsbue.fsbue_name AS Customername, fsofo.fsofo_organizationname AS Partnername FROM fsaqh_airquotationheader fsaqh LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsaqh.fsbue_idcustomer) LEFT JOIN fsofo_officeorganization fsofo ON (fsofo.fsofo_id=fsaqh.fsofo_idpartner) JOIN FSGEC_GeneralCode partytype ON (partytype.fsgec_referencename=''QUOTATIONPARTYTYPE'' AND partytype.fsgec_value=fsaqh.fsaqh_quotationpartytype ) JOIN fsgec_generalcode fsgec_export ON (fsgec_export.fsgec_referencename = ''BOOKINGTYPE'' AND fsgec_export.fsgec_value =fsaqh.fsaqh_isexportimport) WHERE (('''|| p_searchtext|| ''' IS NULL) OR ( (UPPER(fsaqh.fsaqh_referencenumber) LIKE '''|| UPPER (p_searchtext)|| '%'||'''))) '; l_query_cnt := 'SELECT COUNT(fsaqh_id) FROM (' || l_query || ') '; p_Airquotation_count :=0; IF p_page_index > 0 THEN EXECUTE IMMEDIATE l_query_cnt INTO p_Airquotation_count; l_query := l_query || ' order by '; l_query := l_query || p_sort_field; l_query := l_query || ' '; l_query := l_query || p_sort_type; l_query := l_query || ' offset ( ('|| p_page_index|| ' - 1) * '|| p_page_size|| ') rows fetch next '|| p_page_size|| ' rows only '; END IF; OPEN p_Airquotationreference FOR l_query; END IF; p_issuccess := fs_constants.k_success; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_Dual; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END getLov_copyreference_prc; PROCEDURE GetList_fsaqh_Prc ( --search for air quotation p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_fsbue_idcustomer IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE, p_fsaqh_quotationtype IN fsaqh_airquotationheader.fsaqh_quotationtype%TYPE, p_fsaqh_shipmenttype IN fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE, p_fsaqh_referencenumber IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsaqh_validfrom IN fsaqh_airquotationheader.fsaqh_validfrom%TYPE, p_fsaqh_validto IN fsaqh_airquotationheader.fsaqh_validto%TYPE, p_fsbue_idcarrier IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE, p_fsart_idfromAirport IN fsaqt_airquotationtransportstation.fsart_idfromAirport%TYPE, p_fsart_idtoAirport IN fsaqt_airquotationtransportstation.fsart_idtoAirport%TYPE, p_quotationparty IN fsaqh_airquotationheader.fsaqh_quotationtype%TYPE, p_partner IN fsaqh_airquotationheader.fsofo_id%TYPE, p_fsaqh_status IN fsaqh_airquotationheader.fsaqh_status%TYPE, p_page_index IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_page_size IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_sort_field IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE, p_sort_type IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE, p_fsaqh_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsaqh_list OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS l_query CLOB; l_query_cnt CLOB; BEGIN IF FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_fsaqh_quotationtype) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_fsaqh_shipmenttype) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_quotationparty) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_fsaqh_status) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_field) = 'Y' OR FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_type) = 'Y' THEN p_fsaqh_count := 0; OPEN p_fsaqh_list FOR SELECT NULL AS fsaqh_id, NULL AS fsaqh_customername, NULL AS fsaqh_quotationtype, NULL AS fsaqh_quotationtype_description, NULL AS fsaqh_shipmenttype, NULL AS fsaqh_shipmenttype_description, NULL AS fsaqh_referencenumber, NULL AS fsaqh_carriername, NULL AS fsart_fromport, NULL AS fsart_toport, NULL AS fsaqh_validfrom, NULL AS fsaqh_validto, NULL AS fsaqh_status_description --NULL AS fsaqh_versionnumber FROM FSDUL_Dual WHERE 1=2 ; ELSE l_query := ' SELECT fsaqh.fsaqh_id AS fsaqh_id, fsbue_customer.fsbue_name AS fsaqh_customername, fsaqh.fsaqh_quotationtype AS fsaqh_quotationtype, fsgec_quotationtype.fsgec_description AS fsaqh_quotationtype_description, fsaqh.fsaqh_shipmenttype AS fsaqh_shipmenttype, fsgec_shipmenttype.fsgec_description AS fsaqh_shipmenttype_description, fsaqh.fsaqh_referencenumber AS fsaqh_referencenumber, (SELECT listagg (DISTINCT fsbue.fsbue_name,'', '' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqc.fsaqc_id) FROM fsaqc_airquotationcarrier fsaqc JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsaqc.fsbue_idcarrier) WHERE fsaqc.fsaqh_id= fsaqc.fsaqh_id ) AS fsaqh_carriername, fsart_fromairport.fsart_name AS fsart_fromport, fsart_toairport.fsart_name AS fsart_toport, fsaqh.fsaqh_validfrom AS fsaqh_validfrom, fsaqh.fsaqh_validto AS fsaqh_validto, fsgec_status.fsgec_description AS fsaqh_status_description --fsaqh.fsaqh_versionnumber AS fsaqh_versionnumber FROM fsaqh_airquotationheader fsaqh JOIN fsaqt_airquotationtransportstation fsaqt ON (fsaqt.fsaqh_id=fsaqh.fsaqh_id) JOIN FSART_AirportMaster fsart_fromairport ON (fsart_fromairport.fsart_id=fsaqt.fsart_idfromairport) JOIN FSART_AirportMaster fsart_toairport ON (fsart_toairport.fsart_id=fsaqt.fsart_idtoairport) JOIN fsbue_businessentity fsbue_customer ON (fsbue_customer.fsbue_id=fsaqh.fsbue_idcustomer) --JOIN fsaqc_airquotationcarrier fsaqc ON (fsaqc.fsaqh_id=fsaqh.fsaqh_id) -- JOIN fsbue_businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsaqc.fsbue_idcarrier) JOIN FSGEC_GeneralCode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_value=fsaqh.fsaqh_quotationtype AND fsgec_quotationtype.fsgec_referencename=''QUOTATIONTYPE'') JOIN FSGEC_GeneralCode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_value=fsaqh.fsaqh_shipmenttype AND fsgec_shipmenttype.fsgec_referencename=''AIRSHIPMENTTYPE'') JOIN FSGEC_GeneralCode fsgec_status ON (fsgec_status.fsgec_value=fsaqh.fsaqh_status AND fsgec_status.fsgec_referencename=''QUOTATIONSTATUS'') WHERE 1=1 '; IF p_fsaqh_validfrom IS NOT NULL THEN l_query := l_query || ' AND TRUNC(fsaqh.fsaqh_validfrom) >= '''|| TRUNC(p_fsaqh_validfrom) ||'''' ; END IF; IF p_fsaqh_validto IS NOT NULL THEN l_query := l_query || ' AND TRUNC(fsaqh.fsaqh_validto) <= '''|| TRUNC(p_fsaqh_validto)||'''' ; END IF; IF p_fsaqh_status IS NOT NULL THEN l_query := l_query || ' AND fsaqh.fsaqh_status = '''|| p_fsaqh_status|| ''''; END IF; IF p_fsbue_idcustomer IS NOT NULL THEN l_query := l_query || ' AND fsaqh.fsbue_idcustomer = '''|| p_fsbue_idcustomer|| ''' '; END IF; IF p_fsaqh_referencenumber IS NOT NULL THEN l_query := l_query || ' AND fsaqh.fsaqh_referencenumber = '''|| p_fsaqh_referencenumber|| ''''; END IF; IF p_fsaqh_quotationtype IS NOT NULL THEN l_query := l_query || ' AND fsaqh.fsaqh_quotationtype = '''|| p_fsaqh_quotationtype|| ''''; END IF; IF p_fsaqh_shipmenttype IS NOT NULL THEN l_query := l_query || ' AND fsaqh.fsaqh_shipmenttype = '''|| p_fsaqh_shipmenttype|| ''''; END IF; IF p_fsbue_idcarrier IS NOT NULL THEN l_query := l_query || ' AND EXISTS (SELECT ''Y'' FROM fsaqc_airquotationcarrier fsaqc WHERE fsaqc.fsaqh_id=fssqh.fsaqh_id AND fsaqc.fsbue_idcarrier='''|| p_fsbue_idcarrier|| ''')'; END IF; IF p_fsart_idfromAirport IS NOT NULL THEN l_query := l_query || ' AND fsaqt.fsart_idfromAirport ='''|| p_fsart_idfromAirport|| ''''; END IF; IF p_fsart_idtoAirport IS NOT NULL THEN l_query := l_query || ' AND fsaqt.fsart_idtoAirport ='''|| p_fsart_idtoAirport|| ''''; END IF; IF p_partner IS NOT NULL THEN l_query := l_query || ' AND fsaqh.fsofo_idpartner = '''|| p_partner|| ''''; END IF; IF p_quotationparty IS NOT NULL THEN l_query := l_query || ' AND fsaqh.fsaqh_quotationpartytype = '''|| p_quotationparty|| ''''; END IF; l_query_cnt := 'SELECT COUNT(fsaqh_id) FROM (' || l_query || ') '; p_fsaqh_count := 0; IF p_page_index > 0 THEN EXECUTE IMMEDIATE l_query_cnt INTO p_fsaqh_count; l_query := l_query || ' order by '; l_query := l_query || p_sort_field; l_query := l_query || ' '; l_query := l_query || p_sort_type; l_query := l_query || ' offset ( ('|| p_page_index|| ' - 1) * '|| p_page_size|| ') rows fetch next '|| p_page_size|| ' rows only '; END IF; OPEN p_fsaqh_list FOR l_query; END IF; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_Dual; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END GetList_fsaqh_Prc; PROCEDURE Get_airquotationratedetails_Prc ( --Quotation creation p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_airquotationrateheader_details OUT FS_AirQuotationManagement.t_list, p_airquotationrate_details OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN OPEN p_airquotationrateheader_details FOR SELECT fsaqh.fsaqh_id AS fsaqh_id, fsaqh.fsbue_idcustomer AS fsbue_idcustomer, fsbue_customer.fsbue_name AS fsaqh_customername, fsaqh.fsaqh_customerdetails AS fsbue_customerdetail, quotationpartytype.fsgec_description AS quotationpartytype_description , fsaqh.fsaqh_quotationpartytype AS fsaqh_quotationpartytype, fsaqh.fsofo_idpartner AS fsofo_idpartner, fsofo_partner.fsofo_organizationname AS parter_name, --fsbua.fsbua_buildingname||' '||fsbua.fsbua_street||' '||fsbua.fsbua_pobox||' '||fsbua.fsbua_phone||' '||fsbua.fsbua_fax AS fsbue_customerdetail, fsaqh.fsaqh_quotationtype AS fsaqh_quotationtype, fsgec_quotationtype.fsgec_description AS fsaqh_quotationtype_description, fsaqh.fsaqh_shipmenttype AS fsaqh_shipmenttype, fsgec_shipmenttype.fsgec_description AS fsaqh_shipmenttype_description, fsaqh.fsaqh_minimumconfirmedquantity AS fsaqh_minimumconfirmedquantity, fsaqh.fsaqh_minimumconfirmedquantitytype AS fsaqh_minimumconfirmedquantitytype, fsgec_mcq.fsgec_description AS fsaqh_mcqtype_description, fsaqh.fsaqh_weight AS fsaqh_weight, fsaqh.fsmea_id AS fsmea_id, fsmea_weight.fsmea_isocode AS fsaqh_weight_unit, fsaqh.fsaqh_volume AS fsaqh_volume, fsaqh.fsaqh_incoterms AS fsaqh_incoterms, fsgec_incoterms.fsgec_description AS fsaqh_incoterms_description, fsaqh.fscry_idquotation AS fscry_idquotation, fscry.fscry_code AS fscry_code, fsaqh.fsaqh_isexportimport AS fsaqh_isexportimport, fsgec_importexport.fsgec_description AS fsaqh_isexportimport_description, (SELECT listagg (DISTINCT fsaqc.fsbue_idcarrier,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqc.fsaqc_id) FROM fsaqc_airquotationcarrier fsaqc WHERE fsaqc.fsaqh_id = fsaqh.fsaqh_id) AS fsaqh_carrierid, (SELECT listagg (DISTINCT fsbue_carrier.fsbue_name,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqc.fsaqc_id) FROM fsaqc_airquotationcarrier fsaqc JOIN fsbue_businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsaqc.fsbue_idcarrier) WHERE fsaqc.fsaqh_id = fsaqh.fsaqh_id) AS fsaqh_carriername, fsaqh.fsofo_idsalesoffice AS fsofo_idsalesoffice, fsofo.fsofo_organizationname AS sales_office, fsaqh.fsaqh_remarks AS fsaqh_remarks, (SELECT listagg (DISTINCT fsaqd.fsdlm_id,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqd.fsaqd_id) FROM fsaqd_airquotationdeal fsaqd WHERE fsaqd.fsaqh_id = fsaqh.fsaqh_id) AS DEALID, (SELECT listagg (DISTINCT fsdlm.fsdlm_referencenumber,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqd.fsaqd_id) FROM fsaqd_airquotationdeal fsaqd JOIN fsdlm_dealmanagement fsdlm ON (fsdlm.fsdlm_id=fsaqd.fsdlm_id) WHERE fsaqd.fsaqh_id = fsaqh.fsaqh_id) AS fsdlm_referencenumber FROM fsaqh_airquotationheader fsaqh LEFT JOIN fsbue_businessentity fsbue_customer ON (fsbue_customer.fsbue_id=fsaqh.fsbue_idcustomer) LEFT JOIN FSOFO_Officeorganization fsofo ON (fsofo.fsofo_id=fsaqh.fsofo_idsalesoffice) LEFT JOIN FSOFO_Officeorganization fsofo_partner ON (fsofo_partner.fsofo_id=fsaqh.fsofo_idpartner) LEFT JOIN fsmea_measurement fsmea_weight ON (fsmea_weight.fsmea_id=fsaqh.fsmea_id) JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqh.fscry_idquotation) JOIN fsgec_generalcode quotationpartytype ON (quotationpartytype.fsgec_referencename='QUOTATIONPARTYTYPE' AND quotationpartytype.fsgec_value=fsaqh.fsaqh_quotationpartytype ) JOIN FSGEC_GeneralCode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_value=fsaqh.fsaqh_quotationtype AND fsgec_quotationtype.fsgec_referencename='QUOTATIONTYPE') JOIN FSGEC_GeneralCode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_value=fsaqh.fsaqh_shipmenttype AND fsgec_shipmenttype.fsgec_referencename='AIRSHIPMENTTYPE') LEFT JOIN FSGEC_GeneralCode fsgec_mcq ON (fsgec_mcq.fsgec_value=fsaqh.fsaqh_minimumconfirmedquantitytype AND fsgec_mcq.fsgec_referencename='QUOTATIONMCQ') JOIN FSGEC_GeneralCode fsgec_incoterms ON (fsgec_incoterms.fsgec_value=fsaqh.fsaqh_incoterms AND fsgec_incoterms.fsgec_referencename='BOOKINGINCOTERMS') JOIN FSGEC_GeneralCode fsgec_importexport ON (fsgec_importexport.fsgec_value=fsaqh.fsaqh_isexportimport AND fsgec_importexport.fsgec_referencename='BOOKINGTYPE') WHERE fsaqh.fsaqh_id=p_fsaqh_id; OPEN p_airquotationrate_details FOR SELECT fsart_fromport.fsart_name AS fsart_fromport, fsaqt.fsart_idfromAirport AS fsart_idfromAirport, fsart_toport.fsart_name AS fsart_toport, fsaqt.fsart_idtoAirport AS fsart_idtoAirport, (SELECT listagg (DISTINCT transhipment.fsart_name,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqp.fsaqp_id) FROM fsaqp_airquotationtranshipment fsaqp JOIN fsart_airportmaster transhipment ON (transhipment.fsart_id=fsaqp.fsart_idtranshipment) WHERE fsaqp.fsaqt_id = fsaqt.fsaqt_id) AS fsprt_transhipment, (SELECT listagg (DISTINCT fsaqp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqp.fsaqp_id) FROM fsaqp_airquotationtranshipment fsaqp WHERE fsaqp.fsaqt_id = fsaqt.fsaqt_id) AS transhipmentid, (SELECT listagg (DISTINCT fsaqe.fsaqe_cargotype,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqe.fsaqe_id) FROM fsaqe_airquotationequipment fsaqe WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id) AS fsaqe_cargotype, (SELECT listagg (DISTINCT fsaqe.fseqt_sizetypeuld,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqe.fsaqe_id) FROM fsaqe_airquotationequipment fsaqe WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id) AS fseqt_sizetypeuld, (SELECT listagg (DISTINCT fsaqm.fscmg_id,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqm.fsaqm_id) FROM fsaqm_airquotationcommodity fsaqm WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS commodityid, (SELECT listagg (DISTINCT fscmg.fscmg_code,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqm.fsaqm_id) FROM fsaqm_airquotationcommodity fsaqm JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqm.fscmg_id) WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS fscmg_groupcode FROM fsaqt_airquotationtransportstation fsaqt JOIN fsart_airportmaster fsart_fromport ON (fsart_fromport.fsart_id = fsaqt.fsart_idfromAirport) JOIN fsart_airportmaster fsart_toport ON (fsart_toport.fsart_id = fsaqt.fsart_idtoAirport) WHERE fsaqt.fsaqh_id=p_fsaqh_id; p_issuccess := fs_constants.k_success; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_Dual; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END Get_airquotationratedetails_Prc; PROCEDURE Get_Ratefromtariff_Prc ( --get rates from tariff p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_fsbue_idcustomer IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE, p_fssqh_shipmenttype IN fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE, p_fsbue_idcarrier IN fsbue_businessentity.fsbue_name%TYPE, p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE, p_header_details OUT FS_AirQuotationManagement.t_list, p_Tariff_details OUT FS_AirQuotationManagement.t_list, p_weightdetails OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS v_route fsdcl_documentationchangelog.fsdcl_content%TYPE; BEGIN v_route:=REPLACE(p_route,'\',''); OPEN p_header_details FOR WITH route_details AS (SELECT jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType, LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup, LISTAGG(DISTINCT jt.Via, ',') WITHIN GROUP (ORDER BY 1) Viaport FROM JSON_TABLE(v_route,'$[*]' COLUMNS ( Fromportid VARCHAR2(100) PATH '$.From', Toportid VARCHAR2(100) PATH '$.To' , CargoType VARCHAR2(100) PATH '$.CargoType' , UldType VARCHAR2(100) PATH '$.UldType' , NESTED PATH '$.CommodityGroup[*]' COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id') , NESTED PATH '$.Via[*]' COLUMNS (Via VARCHAR2(100) PATH '$.Id') ) ) jt GROUP BY jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType) SELECT fsath.fsath_id AS fsath_id, fsatt.fsatt_id AS fsatt_id, fsath.fsbue_idline AS fsbue_idline, fsbue.fsbue_name AS fsbue_name, fsart_fromport.fsart_name AS fsart_fromport, fsatt.fsart_idfromAirport AS fsart_idfromAirport, fsart_toport.fsart_name AS fsart_toport, fsatt.fsart_idtoAirport AS fsart_idtoAirport, fsatt.fsatt_transitdays AS fsatt_transitdays, (SELECT listagg (DISTINCT fsatp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsatp.fsatp_id) FROM fsatp_airtarifftranshipment fsatp WHERE fsatp.fsatt_id = fsatt.fsatt_id) AS transhipmentid, (SELECT listagg (DISTINCT transhipment.fsart_name,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsatp.fsatp_id) FROM fsatp_airtarifftranshipment fsatp JOIN fsart_airportmaster transhipment ON (transhipment.fsart_id=fsatp.fsart_idtranshipment) WHERE fsatp.fsatt_id = fsatt.fsatt_id) AS transhipment, (SELECT listagg (DISTINCT fsatf.fsatf_dayoffrequency,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsatf.fsatf_id) FROM fsatf_airtarifftransportfrequency fsatf WHERE fsatf.fsatt_id=fsatt.fsatt_id ) AS fsatf_dayoffrequency, (SELECT listagg (DISTINCT fsgec.fsgec_description,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsatf.fsatf_id) FROM fsatf_airtarifftransportfrequency fsatf JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='FREQUENCY' AND fsgec.fsgec_value=fsatf.fsatf_dayoffrequency) WHERE fsatf.fsatt_id=fsatt.fsatt_id ) AS fsatf_dayoffrequency_description FROM fsath_airtariffheader fsath JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id) JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsath.fsbue_idline) JOIN fsart_airportmaster fsart_fromport ON (fsart_fromport.fsart_id = fsatt.fsart_idfromAirport) JOIN fsart_airportmaster fsart_toport ON (fsart_toport.fsart_id = fsatt.fsart_idtoAirport) JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport) WHERE fsath.fsath_status ='P' AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber) FROM fsath_airtariffheader fsath GROUP BY fsath.fsath_referencenumber) AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (p_fsbue_idcustomer IS NULL OR EXISTS (SELECT 'X' FROM fsatb_airtariffcustomer fsatb WHERE fsatb.fsath_id=fsath.fsath_id AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer)) AND EXISTS (SELECT 'X' FROM fsatr_airtariffrate fsatr WHERE fsatr.fsatt_id=fsatt.fsatt_id AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))) AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL)); OPEN p_Tariff_details FOR WITH route_details AS (SELECT jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType, LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup, LISTAGG(DISTINCT jt.Via, ',') WITHIN GROUP (ORDER BY 1) Viaport FROM JSON_TABLE(v_route,'$[*]' COLUMNS ( Fromportid VARCHAR2(100) PATH '$.From', Toportid VARCHAR2(100) PATH '$.To' , CargoType VARCHAR2(100) PATH '$.CargoType' , UldType VARCHAR2(100) PATH '$.UldType' , NESTED PATH '$.CommodityGroup[*]' COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id') , NESTED PATH '$.Via[*]' COLUMNS (Via VARCHAR2(100) PATH '$.Id') ) ) jt GROUP BY jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType) SELECT TO_CHAR(fsath.fsath_id) AS fsath_id, fsatt.fsatt_id AS fsatt_id, fsatr.fsatr_id AS fsatr_id, fschg.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fsatr.fsatr_cargouldtype AS fsatr_cargouldtype, fsgec.fsgec_description AS fsatr_cargouldtype_description, fsatr.fseqt_sizetypeuld AS fseqt_sizetypeuld, fsatr.fscmg_id AS fscmg_id, fscmg.fscmg_code AS fscmg_code, fscmg.fscmg_description AS fscmg_description, fsatr.fsatr_buyingrate AS fsatr_buyingrate, fsatr.fsatr_markuptype AS fsatr_markuptype, fsgec_markuptype.fsgec_description AS fsatr_markuptype_description, fsatr.fsatr_minimummarkuptype As fsatr_minimummarkuptype, fsgec_minmarkuptype.fsgec_description AS fsatr_minmarkuptype_description, fsatr.fsatr_minimumsellingrate AS fsatr_minimumsellingrate, fsatr.fsatr_minimumbuyingrate AS fsatr_minimumbuyingrate, fsatr.fsatr_minimummarkupvalue AS fsatr_minimummarkupvalue, fsatr.fsatr_markupvalue AS fsatr_markupvalue, fsatr.fsatr_sellingrate AS fsatr_sellingrate, fsatr.fsatr_additionaldayfee AS fsatr_additionaldayfee, fsatr.fsatr_pivotweight AS fsatr_pivotweight, (SELECT listagg (DISTINCT fsati.fschg_id,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsati.fsati_id) FROM fsati_airtariffinclusivecharge fsati WHERE fsati.fsatr_id = fsatr.fsatr_id) AS inclusive_chargeid, (SELECT listagg (DISTINCT fschg.fschg_code,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsati.fsati_id) FROM fsati_airtariffinclusivecharge fsati JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsati.fschg_id) WHERE fsati.fsatr_id = fsatr.fsatr_id) AS inclusive_chargedescription, fsatr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code, fsatr.fsatr_remarks AS fsatr_remarks, fsatr.fsatr_ispax AS fsatr_ispax, fsgec_pax.fsgec_description AS fsatr_ispax_description, fsath.fsbue_idline AS fsbue_idline, fsath.fsath_referencenumber AS fsath_referencenumber, fsatt.fsart_idfromAirport AS fsart_idfromAirport, fsatt.fsart_idtoAirport AS fsart_idtoAirport, (SELECT listagg (DISTINCT fsatp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsatp.fsatp_id) FROM fsatp_airtarifftranshipment fsatp WHERE fsatp.fsatt_id = fsatt.fsatt_id) AS transhipmentid FROM fsath_airtariffheader fsath JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id) JOIN fsatr_airtariffrate fsatr ON (fsatr.fsatt_id=fsatt.fsatt_id) LEFT JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='AIRTARIFFCARGOTYPE' AND fsgec.fsgec_value=fsatr.fsatr_cargouldtype) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsatr.fschg_id) JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsatr.fscmg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fsatr.fscry_id) JOIN fsgec_generalcode fsgec_pax ON (fsgec_pax.fsgec_value=fsatr.fsatr_ispax AND fsgec_pax.fsgec_referencename='AIRTARIFFPAX') JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_referencename='AIRMARKUPTYPE' AND fsgec_markuptype.fsgec_value=fsatr.fsatr_markuptype ) LEFT JOIN fsgec_generalcode fsgec_minmarkuptype ON (fsgec_minmarkuptype.fsgec_referencename='MARKUPTYPE' AND fsgec_minmarkuptype.fsgec_value=fsatr.fsatr_minimummarkuptype ) JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport) WHERE fsath.fsath_status ='P' AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber) FROM fsath_airtariffheader fsath GROUP BY fsath.fsath_referencenumber) AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (p_fsbue_idcustomer IS NULL OR EXISTS (SELECT 'X' FROM fsatb_airtariffcustomer fsatb WHERE fsatb.fsath_id=fsath.fsath_id AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer) ) AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL))UNION ALL--SELECT TO_CHAR(fsath.fsath_id) AS fsath_id, fsatt.fsatt_id AS fsatt_id, fsatr.fsatr_id AS fsatr_id, fschg.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fsatr.fsatr_cargouldtype AS fsatr_cargouldtype, fsgec.fsgec_description AS fsatr_cargouldtype_description, fsatr.fseqt_sizetypeuld AS fseqt_sizetypeuld, fsatr.fscmg_id AS fscmg_id, fscmg.fscmg_code AS fscmg_code, fscmg.fscmg_description AS fscmg_description, fsatr.fsatr_buyingrate AS fsatr_buyingrate, fsatr.fsatr_markuptype AS fsatr_markuptype, fsgec_markuptype.fsgec_description AS fsatr_markuptype_description, fsatr.fsatr_minimummarkuptype As fsatr_minimummarkuptype, fsgec_minmarkuptype.fsgec_description AS fsatr_minmarkuptype_description, fsatr.fsatr_minimumsellingrate AS fsatr_minimumsellingrate, fsatr.fsatr_minimumbuyingrate AS fsatr_minimumbuyingrate, fsatr.fsatr_minimummarkupvalue AS fsatr_minimummarkupvalue, fsatr.fsatr_markupvalue AS fsatr_markupvalue, fsatr.fsatr_sellingrate AS fsatr_sellingrate, fsatr.fsatr_additionaldayfee AS fsatr_additionaldayfee, fsatr.fsatr_pivotweight AS fsatr_pivotweight, (SELECT listagg (DISTINCT fsati.fschg_id,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsati.fsati_id) FROM fsati_airtariffinclusivecharge fsati WHERE fsati.fsatr_id = fsatr.fsatr_id) AS inclusive_chargeid, (SELECT listagg (DISTINCT fschg.fschg_code,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsati.fsati_id) FROM fsati_airtariffinclusivecharge fsati JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsati.fschg_id) WHERE fsati.fsatr_id = fsatr.fsatr_id) AS inclusive_chargedescription, fsatr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code, fsatr.fsatr_remarks AS fsatr_remarks, fsatr.fsatr_ispax AS fsatr_ispax, fsgec_pax.fsgec_description AS fsatr_ispax_description, fsath.fsbue_idline AS fsbue_idline, fsath.fsath_referencenumber AS fsath_referencenumber, fsatt.fsart_idfromAirport AS fsart_idfromAirport, fsatt.fsart_idtoAirport AS fsart_idtoAirport, (SELECT listagg (DISTINCT fsatp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsatp.fsatp_id) FROM fsatp_airtarifftranshipment fsatp WHERE fsatp.fsatt_id = fsatt.fsatt_id) AS transhipmentid FROM fsath_airtariffheader fsath JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id) JOIN fsatr_airtariffrate fsatr ON (fsatr.fsatt_id=fsatt.fsatt_id) LEFT JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='AIRTARIFFCARGOTYPE' AND fsgec.fsgec_value=fsatr.fsatr_cargouldtype) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsatr.fschg_id) JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsatr.fscmg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fsatr.fscry_id) JOIN fsgec_generalcode fsgec_pax ON (fsgec_pax.fsgec_value=fsatr.fsatr_ispax AND fsgec_pax.fsgec_referencename='AIRTARIFFPAX') JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_referencename='AIRMARKUPTYPE' AND fsgec_markuptype.fsgec_value=fsatr.fsatr_markuptype ) LEFT JOIN fsgec_generalcode fsgec_minmarkuptype ON (fsgec_minmarkuptype.fsgec_referencename='MARKUPTYPE' AND fsgec_minmarkuptype.fsgec_value=fsatr.fsatr_minimummarkuptype ) JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport) WHERE fsath.fsath_status ='P' AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber) FROM fsath_airtariffheader fsath GROUP BY fsath.fsath_referencenumber) AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (p_fsbue_idcustomer IS NULL OR NOT EXISTS (SELECT 'X' FROM fsatb_airtariffcustomer fsatb WHERE fsatb.fsath_id=fsath.fsath_id AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer) ) AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL)) AND NOT EXISTS (SELECT 'X' FROM fsath_airtariffheader fsath JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id) JOIN fsatr_airtariffrate fsatr ON (fsatr.fsatt_id=fsatt.fsatt_id) LEFT JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='AIRTARIFFCARGOTYPE' AND fsgec.fsgec_value=fsatr.fsatr_cargouldtype) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsatr.fschg_id) JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsatr.fscmg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fsatr.fscry_id) JOIN fsgec_generalcode fsgec_pax ON (fsgec_pax.fsgec_value=fsatr.fsatr_ispax AND fsgec_pax.fsgec_referencename='AIRTARIFFPAX') JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_referencename='AIRMARKUPTYPE' AND fsgec_markuptype.fsgec_value=fsatr.fsatr_markuptype ) LEFT JOIN fsgec_generalcode fsgec_minmarkuptype ON (fsgec_minmarkuptype.fsgec_referencename='MARKUPTYPE' AND fsgec_minmarkuptype.fsgec_value=fsatr.fsatr_minimummarkuptype ) JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport) WHERE fsath.fsath_status ='P' AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber) FROM fsath_airtariffheader fsath GROUP BY fsath.fsath_referencenumber) AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL)) AND EXISTS (SELECT 'X' FROM fsatb_airtariffcustomer fsatb WHERE fsatb.fsath_id=fsath.fsath_id AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer) ) AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL)) UNION ALL SELECT fsast.fsast_uigroupingreference AS fsath_id, fsast.fsast_id AS fsatt_id, fsasr.fsasr_id AS fsatr_id, fschg.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, NULL AS fsatr_cargouldtype, NULL AS fsatr_cargouldtype_description, NULL AS fseqt_sizetypeuld, fsasr.fscmg_id AS fscmg_id, fscmg.fscmg_code AS fscmg_code, fscmg.fscmg_description AS fscmg_description, NULL AS fsatr_buyingrate, fsasr.fsasr_calculationprocedure AS fsatr_markuptype, fsgec_calcprc.fsgec_description AS fsatr_markuptype_description, NULL AS fsatr_minimummarkuptype, NULL AS fsatr_minmarkuptype_description, NULL AS fsatr_minimumsellingrate, NULL AS fsatr_minimumbuyingrate, NULL AS fsatr_minimummarkupvalue, fsasr.fsasr_value AS fsatr_markupvalue, fsasr.fsasr_rate AS fsatr_sellingrate, NULL AS fsatr_additionaldayfee, NULL AS fsatr_pivotweight, NULL AS inclusive_chargeid, NULL AS inclusive_chargedescription, fsasr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code, fsasr.fsasr_remarks AS fsasr_remarks, NULL AS fsatr_ispax, NULL AS fsatr_ispax_description, fsasr.fsbue_idcarrier AS fsbue_idline, NULL AS fsath_referencenumber, fsast.fsart_idfromairport AS fsart_idfromAirport, fsast.fsart_idtoairport AS fsart_idtoAirport, NULL AS transhipmentid FROM fsast_airsurchargetransport fsast JOIN fsasr_airsurchargerate fsasr ON (fsast.fsast_id=fsasr.fsast_id) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsasr.fschg_id) JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsasr.fscmg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fsasr.fscry_id) LEFT JOIN FSGEC_GeneralCode fsgec_calcprc ON (fsgec_calcprc.fsgec_referencename = 'MARKUPTYPE' AND fsgec_calcprc.fsgec_value = fsasr.fsasr_calculationprocedure) JOIN route_details ON (route_details.Fromportid=fsast.fsart_idfromairport AND route_details.Toportid=fsast.fsart_idtoairport) WHERE fsasr.fsasr_shipmenttype = p_fssqh_shipmenttype AND (p_fsbue_idcarrier IS NULL OR fsasr.fsbue_idcarrier IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.CommodityGroup IS NULL OR fsasr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS commodity FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)) ; OPEN p_weightdetails FOR WITH route_details AS (SELECT jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType, LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup, LISTAGG(DISTINCT jt.Via, ',') WITHIN GROUP (ORDER BY 1) Viaport FROM JSON_TABLE(v_route,'$[*]' COLUMNS ( Fromportid VARCHAR2(100) PATH '$.From', Toportid VARCHAR2(100) PATH '$.To' , CargoType VARCHAR2(100) PATH '$.CargoType' , UldType VARCHAR2(100) PATH '$.UldType' , NESTED PATH '$.CommodityGroup[*]' COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id') , NESTED PATH '$.Via[*]' COLUMNS (Via VARCHAR2(100) PATH '$.Id') ) ) jt GROUP BY jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType) SELECT fsatr.fsatr_id AS fsatr_id, fsatm.fsatm_buyingrate AS fsatm_buyingrate, fsatm.fsatm_sellingrate AS fsatm_sellingrate, fsatm.fsatm_markupvalue AS fsatm_markupvalue, fsatd.fsatd_weight AS fsatd_weight, fsatm.fsatm_id AS fsatm_id FROM fsath_airtariffheader fsath JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id) JOIN fsatr_airtariffrate fsatr ON (fsatr.fsatt_id=fsatt.fsatt_id) JOIN fsatm_airtariffslabrate fsatm ON (fsatm.fsatr_id=fsatr.fsatr_id) JOIN FSATD_AirTariffweightSlab fsatd ON (fsatd.fsatd_id=fsatm.fsatd_id) JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport) WHERE fsath.fsath_status ='P' AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (p_fsbue_idcustomer IS NULL OR EXISTS (SELECT 'X' FROM fsatb_airtariffcustomer fsatb WHERE fsatb.fsath_id=fsath.fsath_id AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer)) AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL)) AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber) FROM fsath_airtariffheader fsath GROUP BY fsath.fsath_referencenumber); p_issuccess := fs_constants.k_success; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_Dual; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END Get_Ratefromtariff_Prc; PROCEDURE Get_localcharges_Prc ( --get local charges p_fsofo_id IN fssqh_seaquotationheader.fsofo_id%TYPE, p_fsbue_idcustomer IN fssqh_seaquotationheader.fsbue_idcustomer%TYPE, p_fsofo_idpartner IN fssqh_seaquotationheader.fsofo_idpartner%TYPE, p_fssqh_shipmenttype IN fssqh_seaquotationheader.fssqh_shipmenttype%TYPE, p_fssqh_isexportimport IN fssqh_seaquotationheader.fssqh_isexportimport%TYPE, p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE, p_Tariff_deatils OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fssqh_seaquotationheader.fssqh_shipmenttype %TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN OPEN p_Tariff_deatils FOR WITH route_details AS (SELECT jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType, LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup, LISTAGG(DISTINCT jt.Via, ',') WITHIN GROUP (ORDER BY 1) Viaport FROM JSON_TABLE(REPLACE(p_route,'\',''),'$[*]' COLUMNS ( Fromportid VARCHAR2(100) PATH '$.From', Toportid VARCHAR2(100) PATH '$.To' , CargoType VARCHAR2(100) PATH '$.CargoType' , UldType VARCHAR2(100) PATH '$.UldType' , NESTED PATH '$.CommodityGroup[*]' COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id') , NESTED PATH '$.Via[*]' COLUMNS (Via VARCHAR2(100) PATH '$.Id') ) ) jt GROUP BY jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType) SELECT fsalp.fslap_id AS fsalp_id, fslar.fslar_id AS fslar_id, fschg.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fslar.fslar_basis AS fslar_basis, basis.fsgec_description AS basis_description, fslar.fseqt_sizetype AS fseqt_sizetype, fslar.fscmg_id AS fscmg_id, fscmg.fscmg_code AS fscmg_code, fscmg.fscmg_description AS fscmg_description, fslar.fslar_amount AS fslar_amount, fslar.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code, fslar.fslar_isexportimport AS fslar_isexportimport, fsgec_importexport.fsgec_description AS fsalr_isexportimport_description, fslar.fslar_remarks AS fslar_remarks FROM fslap_localchargeairport fsalp JOIN fslar_localchargeairrate fslar ON (fslar.fslar_uigroupingreference=fsalp.fslap_uigroupingreference AND fslar.fslar_uicardreference = fsalp.fslap_uicardreference) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fslar.fschg_id) JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fslar.fscmg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fslar.fscry_id) JOIN FSGEC_GeneralCode fsgec_importexport ON ( fsgec_importexport.fsgec_referencename='BOOKINGTYPE' AND fsgec_importexport.fsgec_value=fslar.fslar_isexportimport) JOIN FSGEC_GeneralCode basis ON (basis.fsgec_referencename='LOCALCHARGEBASIS' AND basis.fsgec_value=fslar.fslar_basis ) JOIN route_details ON (route_details.Fromportid=fsalp.fsart_idfrom AND route_details.Toportid=fsalp.fsart_idto) WHERE (p_fsbue_idcustomer IS NULL OR fsalp.fsbue_idcustomer=p_fsbue_idcustomer) AND (p_fsofo_idpartner IS NULL OR fsalp.fsofo_idpartner=p_fsofo_idpartner) AND (route_details.UldType IS NULL OR fslar.fseqt_sizetype IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS sizetype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (route_details.CommodityGroup IS NULL OR fslar.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)) AND (p_fssqh_isexportimport IS NULL OR fslar.fslar_isexportimport =p_fssqh_isexportimport); p_issuccess := fs_constants.k_success; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_Dual; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END Get_localcharges_Prc; PROCEDURE Get_airquotationtariff_Prc ( --Retrive quotation details p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsaqt_id IN fsaqh_airquotationheader.fsaqh_remarks%TYPE, p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_airquotationhdrdetails OUT FS_AirQuotationManagement.t_list, p_airquotationport_details OUT FS_AirQuotationManagement.t_list, p_airquotationrate_details OUT FS_AirQuotationManagement.t_list, p_surcharge_details OUT FS_AirQuotationManagement.t_list, p_localcharge_details OUT FS_AirQuotationManagement.t_list, p_weightdetails OUT FS_AirQuotationManagement.t_list, p_precarriage_details OUT FS_AirQuotationManagement.t_list, p_precarriagecharge_details OUT FS_AirQuotationManagement.t_list, p_oncarriage_details OUT FS_AirQuotationManagement.t_list, p_oncarriagecharge_details OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN OPEN p_airquotationhdrdetails FOR SELECT fsaqh.fsaqh_id AS fsaqh_id, fsaqh.fsbue_idcustomer AS fsbue_idcustomer, fsbue_customer.fsbue_name AS fsaqh_customername, fsaqh.fsaqh_customerdetails AS fsbue_customerdetail, quotationpartytype.fsgec_description AS quotationpartytype_description , fsaqh.fsaqh_quotationpartytype AS fsaqh_quotationpartytype, fsaqh.fsofo_idpartner AS fsofo_idpartner, fsofo_partner.fsofo_organizationname AS parter_name, fsaqh.fsaqh_quotationtype AS fsaqh_quotationtype, fsgec_quotationtype.fsgec_description AS fsaqh_quotationtype_description, fsaqh.fsaqh_shipmenttype AS fsaqh_shipmenttype, fsgec_shipmenttype.fsgec_description AS fsaqh_shipmenttype_description, fsaqh.fsaqh_minimumconfirmedquantitytype AS fsaqh_minimumconfirmedquantitytype, fsgec_mcq.fsgec_description AS fsaqh_mcqtype_description, fsaqh.fsaqh_weight AS fsaqh_weight, fsaqh.fsmea_id AS fsmea_id, fsmea_weight.fsmea_isocode AS fsaqh_weight_unit, fsaqh.fsaqh_volume AS fsaqh_volume, fsaqh.fsaqh_incoterms AS fsaqh_incoterms, fsgec_incoterms.fsgec_description AS fsaqh_incoterms_description, fsaqh.fscry_idquotation AS fscry_idquotation, fscry.fscry_code AS fscry_code, fsaqh.fsaqh_isexportimport AS fsaqh_isexportimport, fsgec_importexport.fsgec_description AS fsaqh_isexportimport_description, fsaqh.fsaqh_validfrom AS fsaqh_validfrom, fsaqh.fsaqh_validto AS fsaqh_validto, fsaqh.fsaqh_isshowcarrier AS fsaqh_isshowcarrier, (SELECT listagg (DISTINCT fsaqc.fsbue_idcarrier,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqc.fsaqc_id) FROM fsaqc_airquotationcarrier fsaqc WHERE fsaqc.fsaqh_id = fsaqh.fsaqh_id) AS fsaqh_carrierid, (SELECT listagg (DISTINCT fsbue_carrier.fsbue_name,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqc.fsaqc_id) FROM fsaqc_airquotationcarrier fsaqc JOIN fsbue_businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsaqc.fsbue_idcarrier) WHERE fsaqc.fsaqh_id = fsaqh.fsaqh_id) AS fsaqh_carriername, fsaqh.fsofo_idsalesoffice AS fsofo_idsalesoffice, fsofo.fsofo_organizationname AS sales_office, fsaqh.fsaqh_remarks AS fsaqh_remarks, (SELECT listagg (DISTINCT fsaqd.fsdlm_id,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqd.fsaqd_id) FROM fsaqd_airquotationdeal fsaqd WHERE fsaqd.fsaqh_id = fsaqh.fsaqh_id) AS DEALID, (SELECT listagg (DISTINCT fsdlm.fsdlm_referencenumber,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqd.fsaqd_id) FROM fsaqd_airquotationdeal fsaqd JOIN fsdlm_dealmanagement fsdlm ON (fsdlm.fsdlm_id=fsaqd.fsdlm_id) WHERE fsaqd.fsaqh_id = fsaqh.fsaqh_id) AS fsdlm_referencenumber, fsaqh.fsaqh_minimumconfirmedquantity AS fsaqh_minimumconfirmedquantity, fsaqh.fsaqh_status AS fsaqh_status, fsgec_status.fsgec_description AS fsaqh_status_description, fsaqh.fsaqh_referencenumber AS fsaqh_referencenumber FROM fsaqh_airquotationheader fsaqh LEFT JOIN fsbue_businessentity fsbue_customer ON (fsbue_customer.fsbue_id=fsaqh.fsbue_idcustomer) JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqh.fscry_idquotation) LEFT JOIN FSOFO_Officeorganization fsofo ON (fsofo.fsofo_id=fsaqh.fsofo_idsalesoffice) LEFT JOIN FSOFO_Officeorganization fsofo_partner ON (fsofo_partner.fsofo_id=fsaqh.fsofo_idpartner) LEFT JOIN fsmea_measurement fsmea_weight ON (fsmea_weight.fsmea_id=fsaqh.fsmea_id) JOIN FSGEC_GeneralCode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_value=fsaqh.fsaqh_quotationtype AND fsgec_quotationtype.fsgec_referencename='QUOTATIONTYPE') JOIN FSGEC_GeneralCode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_value=fsaqh.fsaqh_shipmenttype AND fsgec_shipmenttype.fsgec_referencename='AIRSHIPMENTTYPE') JOIN FSGEC_GeneralCode fsgec_incoterms ON (fsgec_incoterms.fsgec_value=fsaqh.fsaqh_incoterms AND fsgec_incoterms.fsgec_referencename='BOOKINGINCOTERMS') LEFT JOIN FSGEC_GeneralCode fsgec_mcq ON (fsgec_mcq.fsgec_value=fsaqh.fsaqh_minimumconfirmedquantitytype AND fsgec_mcq.fsgec_referencename='QUOTATIONMCQ') JOIN FSGEC_GeneralCode fsgec_importexport ON (fsgec_importexport.fsgec_value=fsaqh.fsaqh_isexportimport AND fsgec_importexport.fsgec_referencename='BOOKINGTYPE') JOIN fsgec_generalcode quotationpartytype ON (quotationpartytype.fsgec_referencename='QUOTATIONPARTYTYPE' AND quotationpartytype.fsgec_value=fsaqh.fsaqh_quotationpartytype ) JOIN FSGEC_GeneralCode fsgec_status ON (fsgec_status.fsgec_value=fsaqh.fsaqh_status AND fsgec_status.fsgec_referencename='QUOTATIONSTATUS') WHERE fsaqh.fsaqh_id=p_fsaqh_id; OPEN p_airquotationport_details FOR SELECT DISTINCT fsaqh.fsaqh_id AS fsaqh_id, fsaqc.fsbue_idcarrier AS fsbue_idcarrier, fsbue_carrier.fsbue_name AS fsbue_carriername, fsaqt.fsart_idfromairport AS fsart_idfromairport, fsaqt.fsart_idtoairport AS fsart_idtoairport, fsart_from.fsart_name AS fsart_fromname, fsart_to.fsart_name AS fsart_toname , (SELECT listagg (DISTINCT transhipment.fsart_name,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqp.fsaqp_id) FROM fsaqp_airquotationtranshipment fsaqp JOIN fsart_airportmaster transhipment ON (transhipment.fsart_id=fsaqp.fsart_idtranshipment) WHERE fsaqp.fsaqt_id = fsaqt.fsaqt_id) AS fsprt_transhipment, (SELECT listagg (DISTINCT fsaqp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqp.fsaqp_id) FROM fsaqp_airquotationtranshipment fsaqp WHERE fsaqp.fsaqt_id = fsaqt.fsaqt_id) AS transhipmentid, (SELECT listagg (DISTINCT fsaqm.fscmg_id,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqm.fsaqm_id) FROM fsaqm_airquotationcommodity fsaqm WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS fscmg_id, (SELECT listagg (DISTINCT fscmg.fscmg_code,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqm.fsaqm_id) FROM fsaqm_airquotationcommodity fsaqm JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqm.fscmg_id) WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS fscmg_code, (SELECT listagg (DISTINCT fscmg.fscmg_description,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqm.fsaqm_id) FROM fsaqm_airquotationcommodity fsaqm JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqm.fscmg_id) WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS fscmg_description, (SELECT listagg (DISTINCT fsaqe.fseqt_sizetypeuld,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqe.fsaqe_id) FROM fsaqe_airquotationequipment fsaqe WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id) AS fseqt_sizetypeuld, (SELECT listagg (DISTINCT fsaqe.fsaqe_cargotype,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqe.fsaqe_id) FROM fsaqe_airquotationequipment fsaqe WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id) AS fsaqe_cargotype, (SELECT listagg (DISTINCT cargotype.fsgec_description,',' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqe.fsaqe_id) FROM fsaqe_airquotationequipment fsaqe JOIN fsgec_generalcode cargotype ON (cargotype.fsgec_value=fsaqe.fsaqe_cargotype AND cargotype.fsgec_referencename='AIRTARIFFCARGOTYPE') WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id ) AS fsaqe_cargotype_description, fsaqt.fsaqt_travellingtime AS fsaqt_travellingtime, fsaqt.fsaqt_dayoffrequency AS fsaqt_dayoffrequency, fsgec.fsgec_description AS fsaqt_dayoffrequency_desccription, fsaqt.fsaqt_id AS fsaqt_id FROM fsaqh_airquotationheader fsaqh JOIN fsaqt_airquotationtransportstation fsaqt ON (fsaqt.fsaqh_id=fsaqh.fsaqh_id) JOIN FSART_AirportMaster fsart_from ON (fsart_from.fsart_id=fsaqt.fsart_idfromairport) JOIN FSART_AirportMaster fsart_to ON (fsart_to.fsart_id=fsaqt.fsart_idtoairport) LEFT JOIN fsaqc_airquotationcarrier fsaqc ON (fsaqc.fsaqh_id=fsaqh.fsaqh_id) LEFT JOIN FSBUE_Businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsaqc.fsbue_idcarrier) LEFT JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='FREQUENCY' AND fsgec.fsgec_value=fsaqt.fsaqt_dayoffrequency) WHERE fsaqh.fsaqh_id=p_fsaqh_id ; OPEN p_airquotationrate_details FOR SELECT fsaqr.fsaqt_id AS fsaqt_id, fsaqr.fsaqr_id AS fsaqr_id, fsaqr.fsbue_idcarrier AS fsbue_idcarrier, fsbue.fsbue_name AS carriername, fsaqr.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fsaqr.fsaqr_cargotype AS fsaqr_cargotype, cargotype.fsgec_description AS cargotype_description, fsaqr.fseqt_sizetypeuld AS fseqt_sizetypeuld, fscmg.fscmg_id AS fscmg_id, fscmg.fscmg_code AS fscmg_code, fscmg.fscmg_description AS fscmg_description, fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate, fsaqr.fsaqr_markuptype AS fsaqr_markuptype, fsgec_markuptype.fsgec_description AS fsaqr_markuptype_description, fsaqr.fsaqr_markupvalue AS fsaqr_markupvalue, fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate, fsaqr.fsaqr_mintariffrate AS fsaqr_mintariffrate, fsaqr.fsaqr_minmarkuptype AS fsaqr_minmarkuptype, fsaqr.fsaqr_minmarkupvalue AS fsaqr_minmarkupvalue, fsaqr.fsaqr_minsellingrate AS fsaqr_minsellingrate, fsaqr.fsaqr_additionaldayfee AS fsaqr_additionaldayfee, fsaqr.fsaqr_pivotweight AS fsaqr_pivotweight , (SELECT listagg (DISTINCT fschg.fschg_code,', ' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqi.fsaqi_id) FROM fsaqi_airquotationinclusivecharge fsaqi JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqi.fschg_id) WHERE fsaqi.fsaqr_id = fsaqr.fsaqr_id) AS fsaqi_inclusivechargecode, (SELECT listagg (DISTINCT fsaqi.fschg_id,', ' ON overflow TRUNCATE WITH COUNT) within GROUP (ORDER BY fsaqi.fsaqi_id) FROM fsaqi_airquotationinclusivecharge fsaqi WHERE fsaqi.fsaqr_id = fsaqr.fsaqr_id) AS fsaqi_inclusivechargeid, fscry.fscry_code AS fscry_code, fsaqr.fsaqr_remarks AS fsaqr_remarks, fsaqr.fsaqr_ispax AS fsaqr_ispax, fsgec_pax.fsgec_description AS fsaqr_ispax_description FROM fsaqr_airquotationrate fsaqr JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id) JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqr.fscmg_id) JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqr.fscry_id) LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsaqr.fsbue_idcarrier) JOIN fsgec_generalcode cargotype ON (cargotype.fsgec_value=fsaqr.fsaqr_cargotype AND cargotype.fsgec_referencename='AIRTARIFFCARGOTYPE') JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_value=fsaqr.fsaqr_markuptype AND fsgec_markuptype.fsgec_referencename='CARGOMARKUPTYPE') JOIN fsgec_generalcode fsgec_pax ON (fsgec_pax.fsgec_value=fsaqr.fsaqr_ispax AND fsgec_pax.fsgec_referencename='AIRTARIFFPAX') WHERE EXISTS (SELECT 'X' FROM fsaqt_airquotationtransportstation fsaqt WHERE fsaqt.fsaqt_id=fsaqr.fsaqt_id AND fsaqt.fsaqh_id=p_fsaqh_id AND (p_fsaqt_id IS NULL OR fsaqt.fsaqt_id IN (SELECT REGEXP_SUBSTR (p_fsaqt_id, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsaqt_id,'[^,]+', 1, LEVEL) IS NOT NULL))); OPEN p_weightdetails FOR SELECT fsaqw.fsaqw_weight AS fsaqw_weight, fsaqw.fsaqw_buyingrate AS fsaqw_buyingrate , fsaqw.fsaqw_sellingrate AS fsaqw_sellingrate , fsaqw.fsaqw_markupvalue AS fsaqw_markupvalue, fsaqw.fsaqw_id AS fsaqw_id, fsaqw.fsatm_id AS fsatm_id, fsaqw.fsaqr_id AS fsaqr_id FROM fsaqr_airquotationrate fsaqr JOIN fsaqw_airquotationslabrate fsaqw ON (fsaqw.fsaqr_id=fsaqr.fsaqr_id) WHERE EXISTS (SELECT 'X' FROM fsaqt_airquotationtransportstation fsaqt WHERE fsaqt.fsaqt_id=fsaqr.fsaqt_id AND fsaqt.fsaqh_id=p_fsaqh_id AND (p_fsaqt_id IS NULL OR fsaqt.fsaqt_id IN (SELECT REGEXP_SUBSTR (p_fsaqt_id, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsaqt_id,'[^,]+', 1, LEVEL) IS NOT NULL))); OPEN p_surcharge_details FOR SELECT fsaqr.fsaqt_id AS fsaqt_id, fsaqr.fsaqr_id AS fsaqr_id, fschg.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fscmg.fscmg_id AS fscmg_id, fscmg.fscmg_code AS fscmg_code, fscmg.fscmg_description AS fscmg_description, fsaqr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code, fsaqr.fsaqr_markuptype AS calcproc, fsgec_markuptype.fsgec_description AS calcproc_description, fsaqr.fsaqr_markupvalue AS fsaqr_markupvalue, fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate, fsaqr.fsaqr_remarks AS fsaqr_remarks FROM fsaqr_airquotationrate fsaqr JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id) JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqr.fscmg_id) JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqr.fscry_id) JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_value=fsaqr.fsaqr_markuptype AND fsgec_markuptype.fsgec_referencename='MARKUPTYPE' ) WHERE EXISTS (SELECT 'X' FROM fsaqt_airquotationtransportstation fsaqt WHERE fsaqt.fsaqt_id=fsaqr.fsaqt_id AND fsaqt.fsaqh_id=p_fsaqh_id AND (p_fsaqt_id IS NULL OR fsaqt.fsaqt_id IN (SELECT REGEXP_SUBSTR (p_fsaqt_id, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsaqt_id,'[^,]+', 1, LEVEL) IS NOT NULL))); OPEN p_localcharge_details FOR SELECT fsaqr.fsaqt_id AS fsaqt_id, fsaqr.fsaqr_id AS fsaqr_id, fschg.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fscmg.fscmg_id AS fscmg_id, fscmg.fscmg_code AS fscmg_code, fscmg.fscmg_description AS fscmg_description, fsaqr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code, fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate, fsaqr.fsaqr_remarks AS fsaqr_remarks, fsaqr.fsaqr_basis AS fsaqr_basis, gec_basis.fsgec_description AS gec_basisdescription, fsaqr.fsaqr_isexportimport AS export_import, export.fsgec_description AS export_importdescription FROM fsaqr_airquotationrate fsaqr JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id) JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqr.fscmg_id) JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqr.fscry_id) JOIN fsgec_generalcode gec_basis ON (gec_basis.fsgec_value=fsaqr.fsaqr_basis AND gec_basis.fsgec_referencename='LOCALCHARGEBASIS' ) JOIN FSGEC_GeneralCode export ON (export.fsgec_referencename='BOOKINGTYPE' AND export.fsgec_value=fsaqr.fsaqr_isexportimport ) WHERE EXISTS (SELECT 'X' FROM fsaqt_airquotationtransportstation fsaqt WHERE fsaqt.fsaqt_id=fsaqr.fsaqt_id AND fsaqt.fsaqh_id=p_fsaqh_id AND (p_fsaqt_id IS NULL OR fsaqt.fsaqt_id IN (SELECT REGEXP_SUBSTR (p_fsaqt_id, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsaqt_id,'[^,]+', 1, LEVEL) IS NOT NULL))); OPEN p_precarriage_details FOR SELECT fsaqu.fsaqu_id AS fsaqu_id, NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id) AS fsaqu_idparent, fsaqu.fsloc_idfrom AS fsloc_idfrom, fsloc_fromlocation.fsloc_name AS fromlocation, fsaqu.fsaqu_fromlocationaddress AS fsaqu_fromlocationaddress, fsaqu.fsaqu_zipcodefrom AS fsaqu_zipcodefrom, fsaqu.fsaqu_zipcodeto AS fsaqu_zipcodeto, fsaqu.fsloc_idto AS fsloc_idto, fsloc_tolocation.fsloc_name AS tolocation, fsaqu.fsaqu_modeoftransport AS fsaqu_modeoftransport, fsgec_transportmode.fsgec_description AS fsaqu_modeoftransport_description, fsaqu.fsaqu_transit AS fsaqu_transit, fsaqu.fsaqu_transittype AS fsaqu_transittype, fsgec_transittype.fsgec_description AS fsgec_transittypedescription, fsaqu.fsaqu_specialinstruction AS fsaqu_specialinstruction, fsaqu.fsaqu_tolocationaddress AS fsaqu_tolocationaddress, fsaqu.fsaqu_ismultimode AS fsaqu_ismultimode, fsaqu.fsaqu_ismultistop AS fsaqu_ismultistop, fsaqu.fsaqu_shipmenttype AS fsaqu_shipmenttype FROM FSAQU_AirQuotationIntermodel fsaqu JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsaqu.fsloc_idfrom) JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsaqu.fsloc_idto) JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsaqu.fsaqu_modeoftransport) JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsaqu.fsaqu_transittype) WHERE fsaqu.fsaqh_id=p_fsaqh_id AND fsaqu.fsaqu_shipmenttype='P'; OPEN p_precarriagecharge_details FOR SELECT fsaqr.fsaqr_id AS fsaqr_id, fsaqu.fsaqu_id AS fsaqu_id, fsaqr.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fsaqr.fsaqr_weight AS fsaqr_weight, fsmea.fsmea_isocode AS fsaqr_weight_unit, fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate, fsaqr.fsaqr_markuptype AS fsaqr_markuptype, markuptype.fsgec_description AS fsaqr_markuptype_description, fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate, fsaqr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code FROM FSAQU_AirQuotationIntermodel fsaqu JOIN fsaqr_airquotationrate fsaqr ON (fsaqr.fsaqu_id=fsaqu.fsaqu_id AND fsaqu.fsaqu_idparent IS NULL) JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsaqr.fsmea_id) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fsaqr.fscry_id) JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsaqr.fsaqr_markuptype ) WHERE fsaqu.fsaqh_id=p_fsaqh_id AND fsaqu.fsaqu_shipmenttype='P'; OPEN p_oncarriage_details FOR SELECT fsaqu.fsaqu_id AS fsaqu_id, NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id) AS fsaqu_idparent, fsaqu.fsloc_idfrom AS fsloc_idfrom, fsloc_fromlocation.fsloc_name AS fromlocation, fsaqu.fsaqu_fromlocationaddress AS fsaqu_fromlocationaddress, fsaqu.fsaqu_zipcodefrom AS fsaqu_zipcodefrom, fsaqu.fsaqu_zipcodeto AS fsaqu_zipcodeto, fsaqu.fsloc_idto AS fsloc_idto, fsloc_tolocation.fsloc_name AS tolocation, fsaqu.fsaqu_modeoftransport AS fsaqu_modeoftransport, fsgec_transportmode.fsgec_description AS fsaqu_modeoftransport_description, fsaqu.fsaqu_transit AS fsaqu_transit, fsaqu.fsaqu_transittype AS fsaqu_transittype, fsgec_transittype.fsgec_description AS fsgec_transittypedescription, fsaqu.fsaqu_specialinstruction AS fsaqu_specialinstruction, fsaqu.fsaqu_tolocationaddress AS fsaqu_tolocationaddress, fsaqu.fsaqu_ismultimode AS fsaqu_ismultimode, fsaqu.fsaqu_ismultistop AS fsaqu_ismultistop, fsaqu.fsaqu_shipmenttype AS fsaqu_shipmenttype FROM fsaqu_airquotationintermodel fsaqu JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsaqu.fsloc_idfrom) JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsaqu.fsloc_idto) JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsaqu.fsaqu_modeoftransport) JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsaqu.fsaqu_transittype) WHERE fsaqu.fsaqh_id=p_fsaqh_id AND fsaqu.fsaqu_shipmenttype='O'; OPEN p_oncarriagecharge_details FOR SELECT fsaqr.fsaqr_id AS fsaqr_id, fsaqu.fsaqu_id AS fsaqu_id, fsaqr.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fsaqr.fsaqr_weight AS fsaqr_weight, fsmea.fsmea_isocode AS fsaqr_weight_unit, fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate, fsaqr.fsaqr_markuptype AS fsaqr_markuptype, markuptype.fsgec_description AS fsaqr_markuptype_description, fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate, fsaqr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code FROM FSAQU_AirQuotationIntermodel fsaqu JOIN fsaqr_airquotationrate fsaqr ON (fsaqr.fsaqu_id=fsaqu.fsaqu_id AND fsaqu.fsaqu_idparent IS NULL) JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsaqr.fsmea_id) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fsaqr.fscry_id) JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsaqr.fsaqr_markuptype ) WHERE fsaqu.fsaqh_id=p_fsaqh_id AND fsaqu.fsaqu_shipmenttype='O'; p_issuccess := fs_constants.k_success; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_Dual; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END Get_airquotationtariff_Prc; PROCEDURE Get_intermodel_Prc ( p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE, p_fsaqu_id IN FSAQU_AirQuotationIntermodel.fsaqu_id%TYPE, p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, p_precarriage_details OUT FS_AirQuotationManagement.t_list, p_precarriagecharge_details OUT FS_AirQuotationManagement.t_list, p_oncarriage_details OUT FS_AirQuotationManagement.t_list, p_oncarriagecharge_details OUT FS_AirQuotationManagement.t_list, p_issuccess OUT fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN OPEN p_precarriage_details FOR SELECT fsaqu.fsaqu_id AS fsaqu_id, NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id) AS fsaqu_idparent, fsaqu.fsloc_idfrom AS fsloc_idfrom, fsloc_fromlocation.fsloc_name AS fromlocation, fsaqu.fsaqu_fromlocationaddress AS fsaqu_fromlocationaddress, fsaqu.fsaqu_zipcodefrom AS fsaqu_zipcodefrom, fsaqu.fsaqu_zipcodeto AS fsaqu_zipcodeto, fsaqu.fsloc_idto AS fsloc_idto, fsloc_tolocation.fsloc_name AS tolocation, fsaqu.fsaqu_modeoftransport AS fsaqu_modeoftransport, fsgec_transportmode.fsgec_description AS fsaqu_modeoftransport_description, fsaqu.fsaqu_transit AS fsaqu_transit, fsaqu.fsaqu_transittype AS fsaqu_transittype, fsgec_transittype.fsgec_description AS fsgec_transittypedescription, fsaqu.fsaqu_specialinstruction AS fsaqu_specialinstruction, fsaqu.fsaqu_tolocationaddress AS fsaqu_tolocationaddress, fsaqu.fsaqu_ismultimode AS fsaqu_ismultimode, fsaqu.fsaqu_ismultistop AS fsaqu_ismultistop, fsaqu.fsaqu_shipmenttype AS fsaqu_shipmenttype FROM FSAQU_AirQuotationIntermodel fsaqu JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsaqu.fsloc_idfrom) JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsaqu.fsloc_idto) JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsaqu.fsaqu_modeoftransport) JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsaqu.fsaqu_transittype) WHERE fsaqu.fsaqh_id=p_fsaqh_id AND NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id)=p_fsaqu_id AND fsaqu.fsaqu_shipmenttype='P'; OPEN p_precarriagecharge_details FOR SELECT fsaqr.fsaqr_id AS fsaqr_id, fsaqr.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fsaqr.fsaqr_weight AS fsaqr_weight, fsmea.fsmea_isocode AS fsaqr_weight_unit, fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate, fsaqr.fsaqr_markuptype AS fsaqr_markuptype, markuptype.fsgec_description AS fsaqr_markuptype_description, fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate, fsaqr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code FROM fsaqr_airquotationrate fsaqr JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsaqr.fsmea_id) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fsaqr.fscry_id) JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsaqr.fsaqr_markuptype ) WHERE fsaqr.fsaqu_id= p_fsaqu_id; OPEN p_oncarriage_details FOR SELECT fsaqu.fsaqu_id AS fsaqu_id, NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id) AS fsaqu_idparent, fsaqu.fsloc_idfrom AS fsloc_idfrom, fsloc_fromlocation.fsloc_name AS fromlocation, fsaqu.fsaqu_fromlocationaddress AS fsaqu_fromlocationaddress, fsaqu.fsaqu_zipcodefrom AS fsaqu_zipcodefrom, fsaqu.fsaqu_zipcodeto AS fsaqu_zipcodeto, fsaqu.fsloc_idto AS fsloc_idto, fsloc_tolocation.fsloc_name AS tolocation, fsaqu.fsaqu_modeoftransport AS fsaqu_modeoftransport, fsgec_transportmode.fsgec_description AS fsaqu_modeoftransport_description, fsaqu.fsaqu_transit AS fsaqu_transit, fsaqu.fsaqu_transittype AS fsaqu_transittype, fsgec_transittype.fsgec_description AS fsgec_transittypedescription, fsaqu.fsaqu_specialinstruction AS fsaqu_specialinstruction, fsaqu.fsaqu_tolocationaddress AS fsaqu_tolocationaddress, fsaqu.fsaqu_ismultimode AS fsaqu_ismultimode, fsaqu.fsaqu_ismultistop AS fsaqu_ismultistop, fsaqu.fsaqu_shipmenttype AS fsaqu_shipmenttype FROM fsaqu_airquotationintermodel fsaqu JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsaqu.fsloc_idfrom) JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsaqu.fsloc_idto) JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsaqu.fsaqu_modeoftransport) JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsaqu.fsaqu_transittype) WHERE fsaqu.fsaqh_id=p_fsaqh_id AND NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id)=p_fsaqu_id AND fsaqu.fsaqu_shipmenttype='O'; OPEN p_oncarriagecharge_details FOR SELECT fsaqr.fsaqr_id AS fsaqr_id, fsaqr.fschg_id AS fschg_id, fschg.fschg_code AS fschg_code, fschg.fschg_description AS fschg_description, fsaqr.fsaqr_weight AS fsaqr_weight, fsmea.fsmea_isocode AS fsaqr_weight_unit, fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate, fsaqr.fsaqr_markuptype AS fsaqr_markuptype, markuptype.fsgec_description AS fsaqr_markuptype_description, fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate, fsaqr.fscry_id AS fscry_id, fscry.fscry_code AS fscry_code FROM fsaqr_airquotationrate fsaqr JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsaqr.fsmea_id) JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id) JOIN fscry_currency fscry ON (fscry.fscry_id =fsaqr.fscry_id) JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsaqr.fsaqr_markuptype ) WHERE fsaqr.fsaqu_id= p_fsaqu_id; OPEN p_message FOR SELECT fs_constants.k_success_msgcode AS msg_code, NULL AS msg_value FROM FSDUL_Dual; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END Get_intermodel_Prc; PROCEDURE put_fsaqh_prc( p_fsaqh_id IN OUT FSAQH_AirQuotationHeader.fsaqh_id%TYPE, p_fsofo_id IN FSAQH_AirQuotationHeader.fsofo_id%TYPE, p_fsaqh_referencenumber IN OUT FSAQH_AirQuotationHeader.fsaqh_referencenumber%TYPE, p_fsaqh_referencedate IN FSAQH_AirQuotationHeader.fsaqh_referencedate%TYPE, p_fsbue_idcustomer IN FSAQH_AirQuotationHeader.fsbue_idcustomer%TYPE, p_fsofo_idsalesoffice IN FSAQH_AirQuotationHeader.fsofo_idsalesoffice%TYPE, p_fsaqh_customerdetails IN FSAQH_AirQuotationHeader.fsaqh_customerdetails%TYPE, p_fsaqh_quotationtype IN FSAQH_AirQuotationHeader.fsaqh_quotationtype%TYPE, p_fsaqh_shipmenttype IN FSAQH_AirQuotationHeader.fsaqh_shipmenttype%TYPE, p_fsaqh_minimumconfirmedquantity IN FSAQH_AirQuotationHeader.fsaqh_minimumconfirmedquantity%TYPE, p_fsaqh_minimumconfirmedquantitytype IN FSAQH_AirQuotationHeader.fsaqh_minimumconfirmedquantitytype%TYPE, p_fsaqh_incoterms IN FSAQH_AirQuotationHeader.fsaqh_incoterms%TYPE, p_fsaqh_isexportimport IN FSAQH_AirQuotationHeader.fsaqh_isexportimport%TYPE, p_fscry_idquotation IN FSAQH_AirQuotationHeader.fscry_idquotation%TYPE, p_fsaqh_validfrom IN FSAQH_AirQuotationHeader.fsaqh_validfrom%TYPE, p_fsaqh_validto IN FSAQH_AirQuotationHeader.fsaqh_validto%TYPE, p_fsaqh_weight IN FSAQH_AirQuotationHeader.fsaqh_weight%TYPE, p_fsmea_id IN FSAQH_AirQuotationHeader.fsmea_id%TYPE, p_fsaqh_volume IN FSAQH_AirQuotationHeader.fsaqh_volume%TYPE, p_fsaqh_quotationpartytype IN FSAQH_AirQuotationHeader.fsaqh_quotationpartytype%TYPE, p_fsofo_idpartner IN FSAQH_AirQuotationHeader.fsofo_idpartner%TYPE, p_fsaqh_isshowcarrier IN FSAQH_AirQuotationHeader.fsaqh_isshowcarrier%TYPE, p_fsaqh_withdrawreason IN FSAQH_AirQuotationHeader.fsaqh_withdrawreason%TYPE, p_fsaqh_status IN FSAQH_AirQuotationHeader.fsaqh_status%TYPE, p_fsaqh_remarks IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_user_id IN FSAQH_AirQuotationHeader.fsaqh_createdby%TYPE, p_record_status IN FSAQH_AirQuotationHeader.fsaqh_purgeyear%TYPE, p_issuccess OUT FSAQH_AirQuotationHeader.fsaqh_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS v_fsaqh_referencenumber FSAQH_AirQuotationHeader.fsaqh_referencenumber%TYPE; BEGIN IF p_record_status =fs_constants.k_insert_status THEN SELECT 'QHA'||LPAD(quotationair_seq.NEXTVAL,7,0) INTO v_fsaqh_referencenumber FROM fsdul_dual; p_fsaqh_referencenumber := v_fsaqh_referencenumber; END IF; FSAQH_AirQuotationHeader_PKG.put_fsaqh_prc( p_fsaqh_id => p_fsaqh_id , p_fsofo_id => p_fsofo_id , p_fsaqh_referencenumber => p_fsaqh_referencenumber , p_fsaqh_referencedate => p_fsaqh_referencedate , p_fsbue_idcustomer => p_fsbue_idcustomer , p_fsofo_idsalesoffice => p_fsofo_idsalesoffice , p_fsaqh_customerdetails => p_fsaqh_customerdetails , p_fsaqh_quotationtype => p_fsaqh_quotationtype , p_fsaqh_shipmenttype => p_fsaqh_shipmenttype , p_fsaqh_minimumconfirmedquantity => p_fsaqh_minimumconfirmedquantity , p_fsaqh_minimumconfirmedquantitytype => p_fsaqh_minimumconfirmedquantitytype , p_fsaqh_incoterms => p_fsaqh_incoterms , p_fsaqh_isexportimport => p_fsaqh_isexportimport , p_fscry_idquotation => p_fscry_idquotation , p_fsaqh_validfrom => p_fsaqh_validfrom , p_fsaqh_validto => p_fsaqh_validto , p_fsaqh_weight => p_fsaqh_weight , p_fsmea_id => p_fsmea_id , p_fsaqh_volume => p_fsaqh_volume , p_fsaqh_status => p_fsaqh_status , p_fsaqh_remarks => p_fsaqh_remarks , p_fsaqh_quotationpartytype => p_fsaqh_quotationpartytype, p_fsofo_idpartner => p_fsofo_idpartner, p_fsaqh_isshowcarrier => p_fsaqh_isshowcarrier, p_fsaqh_withdrawreason => p_fsaqh_withdrawreason, p_fsaqh_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaqh_prc; PROCEDURE put_fsaqc_prc( p_fsaqc_id IN OUT FSAQC_AirQuotationCarrier.fsaqc_id%TYPE, p_fsofo_id IN FSAQC_AirQuotationCarrier.fsofo_id%TYPE, p_fsaqh_id IN FSAQC_AirQuotationCarrier.fsaqh_id%TYPE, p_fsbue_idcarrier IN FSAQC_AirQuotationCarrier.fsbue_idcarrier%TYPE, p_user_id IN FSAQC_AirQuotationCarrier.fsaqc_createdby%TYPE, p_record_status IN FSAQC_AirQuotationCarrier.fsaqc_purgeyear%TYPE, p_issuccess OUT FSAQC_AirQuotationCarrier.fsaqc_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN FSAQC_AirQuotationCarrier_PKG.put_fsaqc_prc( p_fsaqc_id => p_fsaqc_id , p_fsofo_id => p_fsofo_id , p_fsaqh_id => p_fsaqh_id , p_fsbue_idcarrier => p_fsbue_idcarrier , p_fsaqc_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaqc_prc; PROCEDURE put_fsaql_prc( p_fsaql_id IN OUT FSAQL_AirQuotationEmail.fsaql_id%TYPE, p_fsofo_id IN FSAQL_AirQuotationEmail.fsofo_id%TYPE, p_fsaqh_id IN FSAQL_AirQuotationEmail.fsaqh_id%TYPE, p_fsaql_email IN FSAQH_AirQuotationheader.fsaqh_remarks%TYPE, p_user_id IN FSAQL_AirQuotationEmail.fsaql_createdby%TYPE, p_record_status IN FSAQL_AirQuotationEmail.fsaql_purgeyear%TYPE, p_issuccess OUT FSAQL_AirQuotationEmail.fsaql_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN FOR i IN (SELECT REGEXP_SUBSTR (p_fsaql_email, '[^,]+', 1,LEVEL) AS fsaql_email FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsaql_email,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP FSAQL_AirQuotationEmail_PKG.put_fsaql_prc( p_fsaql_id => p_fsaql_id , p_fsofo_id => p_fsofo_id , p_fsaqh_id => p_fsaqh_id , p_fsaql_email => i.fsaql_email , p_fsaql_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaql_prc; PROCEDURE put_fsaqd_prc( p_fsaqd_id IN OUT FSAQD_AirQuotationDeal.fsaqd_id%TYPE, p_fsofo_id IN FSAQD_AirQuotationDeal.fsofo_id%TYPE, p_fsaqh_id IN FSAQD_AirQuotationDeal.fsaqh_id%TYPE, p_fsdlm_id IN FSAQD_AirQuotationDeal.fsdlm_id%TYPE, p_user_id IN FSAQD_AirQuotationDeal.fsaqd_createdby%TYPE, p_record_status IN FSAQD_AirQuotationDeal.fsaqd_purgeyear%TYPE, p_issuccess OUT FSAQD_AirQuotationDeal.fsaqd_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN FSAQD_AirQuotationDeal_PKG.put_fsaqd_prc( p_fsaqd_id => p_fsaqd_id , p_fsofo_id => p_fsofo_id , p_fsaqh_id => p_fsaqh_id , p_fsdlm_id => p_fsdlm_id , p_fsaqd_purgeyear => TO_CHAR(SYSDATE,'YYYY'), p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaqd_prc; PROCEDURE put_fsaqt_prc( p_fsaqt_id IN OUT FSAQT_AirQuotationTransportStation.fsaqt_id%TYPE, p_fsofo_id IN FSAQT_AirQuotationTransportStation.fsofo_id%TYPE, p_fsaqh_id IN FSAQT_AirQuotationTransportStation.fsaqh_id%TYPE, p_fsart_fromport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_fsart_toport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_transhipmentport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_fsaqt_travellingtime IN FSAQT_AirQuotationTransportStation.fsaqt_travellingtime%TYPE, p_fsaqt_dayoffrequency IN FSAQT_AirQuotationTransportStation.fsaqt_dayoffrequency%TYPE, p_routeexist IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE, p_user_id IN FSAQT_AirQuotationTransportStation.fsaqt_createdby%TYPE, p_record_status IN FSAQT_AirQuotationTransportStation.fsaqt_purgeyear%TYPE, p_issuccess OUT FSAQT_AirQuotationTransportStation.fsaqt_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS v_transhipment_sequence fsaqt_airquotationtransportstation.fsaqt_id%TYPE; v_fsaqp_id fsaqt_airquotationtransportstation.fsaqt_id%TYPE; BEGIN IF p_record_status=fs_constants.k_update_status AND p_routeexist='Y' AND p_fsaqt_id IS NOT NULL THEN UPDATE fsaqi_airquotationinclusivecharge fsaqi SET fsaqi.fsaqi_lastupdatedby=p_user_id,fsaqi.fsaqi_lastupdatedon=SYSDATE WHERE fsaqi.fsaqr_id IN (SELECT fsaqr.fsaqr_id FROM fsaqr_airQuotationRate fsaqr WHERE fsaqr.fsaqt_id=p_fsaqt_id); DELETE FROM fsaqi_airquotationinclusivecharge fsaqi WHERE fsaqi.fsaqr_id IN (SELECT fsaqr.fsaqr_id FROM fsaqr_airQuotationRate fsaqr WHERE fsaqr.fsaqt_id=p_fsaqt_id); UPDATE fsaqr_airQuotationRate fsaqr SET fsaqr.fsaqr_lastupdatedby=p_user_id,fsaqr.fsaqr_lastupdatedon=SYSDATE WHERE fsaqr.fsaqt_id=p_fsaqt_id; DELETE FROM fsaqr_airQuotationRate fsaqr WHERE fsaqr.fsaqt_id=p_fsaqt_id; END IF; FSAQT_AirQuotationTransportStation_PKG.put_fsaqt_prc( p_fsaqt_id => p_fsaqt_id , p_fsofo_id => p_fsofo_id , p_fsaqh_id => p_fsaqh_id , p_fsart_idfromAirport => p_fsart_fromport , p_fsart_idtoAirport => p_fsart_toport , p_fsaqt_travellingtime => p_fsaqt_travellingtime, p_fsaqt_dayoffrequency => p_fsaqt_dayoffrequency, p_fsaqt_purgeyear => TO_CHAR(SYSDATE,'YYYY'), p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); IF p_issuccess=fs_constants.k_success AND p_record_status=fs_constants.k_insert_status AND p_transhipmentport IS NOT NULL THEN FOR k in (SELECT REGEXP_SUBSTR (p_transhipmentport, '[^,]+', 1,LEVEL) AS transhipmentport FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_transhipmentport,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP v_transhipment_sequence:= NVL(v_transhipment_sequence,0)+ 1; fsaqp_airquotationtranshipment_pkg.put_fsaqp_prc( p_fsaqp_id => v_fsaqp_id , p_fsofo_id => p_fsofo_id , p_fsaqt_id => p_fsaqt_id , p_fsaqp_sequence => v_transhipment_sequence , p_fsart_idtranshipment => k.transhipmentport , p_fsaqp_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; ELSIF p_issuccess=fs_constants.k_success AND p_record_status=fs_constants.k_update_status THEN DELETE FROM fsaqp_airquotationtranshipment WHERE fsaqt_id=p_fsaqt_id; IF p_transhipmentport IS NOT NULL THEN FOR k in (SELECT REGEXP_SUBSTR (p_transhipmentport, '[^,]+', 1,LEVEL) AS transhipmentport FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_transhipmentport,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP v_transhipment_sequence:= NVL(v_transhipment_sequence,0)+ 1; fsaqp_airquotationtranshipment_pkg.put_fsaqp_prc( p_fsaqp_id => v_fsaqp_id , p_fsofo_id => p_fsofo_id , p_fsaqt_id => p_fsaqt_id , p_fsaqp_sequence => v_transhipment_sequence , p_fsart_idtranshipment => k.transhipmentport , p_fsaqp_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => fs_constants.k_insert_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; END IF; END IF; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaqt_prc; PROCEDURE put_fsaqe_prc( p_fsaqe_id IN OUT FSAQE_AirQuotationEquipment.fsaqe_id%TYPE, p_fsofo_id IN FSAQE_AirQuotationEquipment.fsofo_id%TYPE, p_fsaqt_id IN FSAQE_AirQuotationEquipment.fsaqt_id%TYPE, p_fseqt_sizetypeuld IN FSAQE_AirQuotationEquipment.fseqt_sizetypeuld%TYPE, p_fsaqe_cargotype IN FSAQE_AirQuotationEquipment.fsaqe_cargotype%TYPE, p_fscmg_group IN FSAQH_AirQuotationheader.fsaqh_remarks%TYPE, p_user_id IN FSAQE_AirQuotationEquipment.fsaqe_createdby%TYPE, p_record_status IN FSAQE_AirQuotationEquipment.fsaqe_purgeyear%TYPE, p_issuccess OUT FSAQE_AirQuotationEquipment.fsaqe_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS v_fsaqm_id fsaqe_airquotationequipment.fsaqe_id%TYPE; BEGIN IF p_record_status=fs_constants.k_insert_status AND p_fsaqe_cargotype IS NOT NULL THEN FOR i IN (SELECT REGEXP_SUBSTR (p_fsaqe_cargotype, '[^,]+', 1,LEVEL) AS fsaqe_cargotype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsaqe_cargotype,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP FSAQE_AirQuotationEquipment_PKG.put_fsaqe_prc( p_fsaqe_id => p_fsaqe_id , p_fsofo_id => p_fsofo_id , p_fsaqt_id => p_fsaqt_id , p_fseqt_sizetypeuld => p_fseqt_sizetypeuld , p_fsaqe_cargotype => i.fsaqe_cargotype , p_fsaqe_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; ELSIF p_record_status=fs_constants.k_update_status THEN DELETE FROM FSAQE_AirQuotationEquipment WHERE fsaqt_id=p_fsaqt_id; IF p_fsaqe_cargotype IS NOT NULL THEN FOR i IN (SELECT REGEXP_SUBSTR (p_fsaqe_cargotype, '[^,]+', 1,LEVEL) AS fsaqe_cargotype FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fsaqe_cargotype,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP FSAQE_AirQuotationEquipment_PKG.put_fsaqe_prc( p_fsaqe_id => p_fsaqe_id , p_fsofo_id => p_fsofo_id , p_fsaqt_id => p_fsaqt_id , p_fseqt_sizetypeuld => p_fseqt_sizetypeuld , p_fsaqe_cargotype => i.fsaqe_cargotype , p_fsaqe_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => fs_constants.k_insert_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; END IF; END IF; IF p_record_status=fs_constants.k_insert_status AND p_fscmg_group IS NOT NULL THEN FOR j IN (SELECT REGEXP_SUBSTR (p_fscmg_group, '[^,]+', 1,LEVEL) AS fscmg_group FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fscmg_group,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP FSAQM_AirQuotationCommodity_PKG.put_fsaqm_prc( p_fsaqm_id => v_fsaqm_id , p_fsofo_id => p_fsofo_id , p_fsaqt_id => p_fsaqt_id , p_fscmg_id => j.fscmg_group , p_fsaqm_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; ELSIF p_record_status=fs_constants.k_update_status THEN DELETE FROM FSAQM_AirQuotationCommodity WHERE fsaqt_id=p_fsaqt_id; IF p_fscmg_group IS NOT NULL THEN FOR j IN (SELECT REGEXP_SUBSTR (p_fscmg_group, '[^,]+', 1,LEVEL) AS fscmg_group FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_fscmg_group,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP FSAQM_AirQuotationCommodity_PKG.put_fsaqm_prc( p_fsaqm_id => v_fsaqm_id , p_fsofo_id => p_fsofo_id , p_fsaqt_id => p_fsaqt_id , p_fscmg_id => j.fscmg_group , p_fsaqm_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; END IF; END IF; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaqe_prc; PROCEDURE put_fsaqu_prc( p_fsaqu_id IN OUT FSAQU_AirQuotationIntermodel.fsaqu_id%TYPE, p_fsofo_id IN FSAQU_AirQuotationIntermodel.fsofo_id%TYPE, p_fsaqh_id IN FSAQU_AirQuotationIntermodel.fsaqh_id%TYPE, p_fsaqu_shipmenttype IN FSAQU_AirQuotationIntermodel.fsaqu_shipmenttype%TYPE, p_fsaqu_ismultimode IN FSAQU_AirQuotationIntermodel.fsaqu_ismultimode%TYPE, p_fsaqu_ismultistop IN FSAQU_AirQuotationIntermodel.fsaqu_ismultistop%TYPE, p_fsloc_idfrom IN FSAQU_AirQuotationIntermodel.fsloc_idfrom%TYPE, p_fsaqu_zipcodefrom IN FSAQU_AirQuotationIntermodel.fsaqu_zipcodefrom%TYPE, p_fsaqu_zipcodeto IN FSAQU_AirQuotationIntermodel.fsaqu_zipcodeto%TYPE, p_fsloc_idto IN FSAQU_AirQuotationIntermodel.fsloc_idto%TYPE, p_fsaqu_modeoftransport IN FSAQU_AirQuotationIntermodel.fsaqu_modeoftransport%TYPE, p_fsaqu_transit IN FSAQU_AirQuotationIntermodel.fsaqu_transit%TYPE, p_fsaqu_transittype IN FSAQU_AirQuotationIntermodel.fsaqu_transittype%TYPE, p_fsaqu_specialinstruction IN FSAQU_AirQuotationIntermodel.fsaqu_specialinstruction%TYPE, p_fsaqu_fromlocationaddress IN FSAQU_AirQuotationIntermodel.fsaqu_fromlocationaddress%TYPE, p_fsaqu_tolocationaddress IN FSAQU_AirQuotationIntermodel.fsaqu_tolocationaddress%TYPE, p_fsaqu_locationtype IN FSAQU_AirQuotationIntermodel.fsaqu_locationtype%TYPE, p_fsaqu_idparent IN FSAQU_AirQuotationIntermodel.fsaqu_idparent%TYPE, p_fsaqu_purgeyear IN FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE, p_user_id IN FSAQU_AirQuotationIntermodel.fsaqu_createdby%TYPE, p_record_status IN FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE, p_issuccess OUT FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN FSAQU_AirQuotationIntermodel_PKG.put_fsaqu_prc ( p_fsaqu_id => p_fsaqu_id , p_fsofo_id => p_fsofo_id , p_fsaqh_id => p_fsaqh_id , p_fsaqu_shipmenttype => p_fsaqu_shipmenttype , p_fsaqu_ismultimode => p_fsaqu_ismultimode , p_fsaqu_ismultistop => p_fsaqu_ismultistop , p_fsloc_idfrom => p_fsloc_idfrom , p_fsaqu_zipcodefrom => p_fsaqu_zipcodefrom , p_fsaqu_zipcodeto => p_fsaqu_zipcodeto , p_fsloc_idto => p_fsloc_idto , p_fsaqu_modeoftransport => p_fsaqu_modeoftransport , p_fsaqu_transit => p_fsaqu_transit , p_fsaqu_transittype => p_fsaqu_transittype , p_fsaqu_specialinstruction => p_fsaqu_specialinstruction , p_fsaqu_fromlocationaddress => p_fsaqu_fromlocationaddress , p_fsaqu_tolocationaddress => p_fsaqu_tolocationaddress , p_fsaqu_locationtype => p_fsaqu_locationtype , p_fsaqu_idparent => p_fsaqu_idparent , p_fsaqu_purgeyear => p_fsaqu_purgeyear , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaqu_prc; PROCEDURE put_fsaqr_prc( p_fsaqr_id IN OUT FSAQR_AirQuotationRate.fsaqr_id%TYPE, p_fsofo_id IN FSAQR_AirQuotationRate.fsofo_id%TYPE, p_fsaqt_id IN FSAQR_AirQuotationRate.fsaqt_id%TYPE, p_fsaqu_id IN FSAQR_AirQuotationRate.fsaqu_id%TYPE, p_fsatr_id IN FSAQR_AirQuotationRate.fsatr_id%TYPE, p_fsasr_id IN FSAQR_AirQuotationRate.fsasr_id%TYPE, p_fslar_id IN FSAQR_AirQuotationRate.fslar_id%TYPE, p_fschg_id IN FSAQR_AirQuotationRate.fschg_id%TYPE, p_fsaqr_basis IN FSAQR_AirQuotationRate.fsaqr_basis%TYPE, p_fsaqr_cargotype IN FSAQR_AirQuotationRate.fsaqr_cargotype%TYPE, p_fseqt_sizetypeuld IN FSAQR_AirQuotationRate.fseqt_sizetypeuld%TYPE, p_fscmg_id IN FSAQR_AirQuotationRate.fscmg_id%TYPE, p_fsaqr_weighttype IN FSAQR_AirQuotationRate.fsaqr_weighttype%TYPE, p_fscry_id IN FSAQR_AirQuotationRate.fscry_id%TYPE, p_fsaqr_ispax IN FSAQR_AirQuotationRate.fsaqr_ispax%TYPE, p_fsaqr_weight IN FSAQR_AirQuotationRate.fsaqr_weight%TYPE, p_fsmea_id IN FSAQR_AirQuotationRate.fsmea_id%TYPE, p_fsaqr_tariffrate IN FSAQR_AirQuotationRate.fsaqr_tariffrate%TYPE, p_fsaqr_markuptype IN FSAQR_AirQuotationRate.fsaqr_markuptype%TYPE, p_fsaqr_markupvalue IN FSAQR_AirQuotationRate.fsaqr_markupvalue%TYPE, p_fsaqr_sellingrate IN FSAQR_AirQuotationRate.fsaqr_sellingrate%TYPE, p_fsaqr_mintariffrate IN FSAQR_AirQuotationRate.fsaqr_mintariffrate%TYPE, p_fsaqr_minmarkuptype IN FSAQR_AirQuotationRate.fsaqr_minmarkuptype%TYPE, p_fsaqr_minmarkupvalue IN FSAQR_AirQuotationRate.fsaqr_minmarkupvalue%TYPE, p_fsaqr_minsellingrate IN FSAQR_AirQuotationRate.fsaqr_minsellingrate%TYPE, p_fsaqr_additionaldayfee IN FSAQR_AirQuotationRate.fsaqr_additionaldayfee%TYPE, p_fsaqr_pivotweight IN FSAQR_AirQuotationRate.fsaqr_pivotweight%TYPE, p_fsbue_idcarrier IN FSAQR_AirQuotationRate.fsbue_idcarrier%TYPE, p_fsaqr_isexportimport IN FSAQR_AirQuotationRate.fsaqr_isexportimport%TYPE, p_fsaqr_remarks IN FSAQR_AirQuotationRate.fsaqr_remarks%TYPE, p_inclusivecharge IN FSAQR_AirQuotationRate.fsaqr_remarks%TYPE, p_user_id IN FSAQR_AirQuotationRate.fsaqr_createdby%TYPE, p_record_status IN FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE, p_issuccess OUT FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS v_fsaqi_id fsaqi_airquotationinclusivecharge.fsaqi_id%TYPE; BEGIN FSAQR_AirQuotationRate_PKG.put_fsaqr_prc( p_fsaqr_id => p_fsaqr_id , p_fsofo_id => p_fsofo_id , p_fsaqt_id => p_fsaqt_id , p_fsaqu_id => p_fsaqu_id , p_fsatr_id => p_fsatr_id , p_fsasr_id => p_fsasr_id , p_fslar_id => p_fslar_id , p_fschg_id => p_fschg_id , p_fsaqr_basis => p_fsaqr_basis , p_fsaqr_cargotype => p_fsaqr_cargotype , p_fseqt_sizetypeuld => p_fseqt_sizetypeuld , p_fscmg_id => p_fscmg_id , p_fsaqr_weighttype => p_fsaqr_weighttype , p_fscry_id => p_fscry_id , p_fsaqr_ispax => p_fsaqr_ispax , p_fsaqr_weight => p_fsaqr_weight , p_fsmea_id => p_fsmea_id , p_fsaqr_tariffrate => p_fsaqr_tariffrate , p_fsaqr_markuptype => p_fsaqr_markuptype , p_fsaqr_markupvalue => p_fsaqr_markupvalue , p_fsaqr_sellingrate => p_fsaqr_sellingrate , p_fsaqr_mintariffrate => p_fsaqr_mintariffrate , p_fsaqr_minmarkuptype => p_fsaqr_minmarkuptype , p_fsaqr_minmarkupvalue => p_fsaqr_minmarkupvalue , p_fsaqr_minsellingrate => p_fsaqr_minsellingrate , p_fsaqr_remarks => p_fsaqr_remarks , p_fsaqr_additionaldayfee => p_fsaqr_additionaldayfee , p_fsaqr_pivotweight => p_fsaqr_pivotweight , p_fsbue_idcarrier => p_fsbue_idcarrier, p_fsaqr_isexportimport => p_fsaqr_isexportimport, p_fsaqr_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); IF p_inclusivecharge IS NOT NULL AND p_issuccess=fs_constants.k_success AND p_record_status=fs_constants.k_insert_status THEN FOR i IN (SELECT REGEXP_SUBSTR (p_inclusivecharge, '[^,]+', 1,LEVEL) AS fschg_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_inclusivecharge,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP fsaqi_airquotationinclusivecharge_PKG.put_fsaqi_prc( p_fsaqi_id => v_fsaqi_id , p_fsofo_id => p_fsofo_id , p_fsaqr_id => p_fsaqr_id , p_fschg_id => i.fschg_id , p_fsaqi_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => p_record_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; ELSIF p_issuccess=fs_constants.k_success AND p_record_status=fs_constants.k_update_status THEN DELETE FROM fsaqi_airquotationinclusivecharge WHERE fsaqr_id=p_fsaqr_id; IF p_inclusivecharge IS NOT NULL THEN FOR i IN (SELECT REGEXP_SUBSTR (p_inclusivecharge, '[^,]+', 1,LEVEL) AS fschg_id FROM FSDUL_DUAL CONNECT BY REGEXP_SUBSTR (p_inclusivecharge,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP fsaqi_airquotationinclusivecharge_PKG.put_fsaqi_prc( p_fsaqi_id => v_fsaqi_id , p_fsofo_id => p_fsofo_id , p_fsaqr_id => p_fsaqr_id , p_fschg_id => i.fschg_id , p_fsaqi_purgeyear => TO_CHAR(SYSDATE,'YYYY') , p_user_id => p_user_id , p_record_status => fs_constants.k_insert_status , p_issuccess => p_issuccess , p_message => p_message ); END LOOP; END IF; END IF; EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaqr_prc; PROCEDURE put_fsaqw_prc( p_fsaqw_id IN OUT FSAQW_AirQuotationSlabRate.fsaqw_id%TYPE, p_fsofo_id IN FSAQW_AirQuotationSlabRate.fsofo_id%TYPE, p_fsaqr_id IN FSAQW_AirQuotationSlabRate.fsaqr_id%TYPE, p_fsatm_id IN FSAQW_AirQuotationSlabRate.fsatm_id%TYPE, p_fsaqw_buyingrate IN FSAQW_AirQuotationSlabRate.fsaqw_buyingrate%TYPE, p_fsaqw_markupvalue IN FSAQW_AirQuotationSlabRate.fsaqw_markupvalue%TYPE, p_fsaqw_sellingrate IN FSAQW_AirQuotationSlabRate.fsaqw_sellingrate%TYPE, P_fsaqw_weight IN FSAQW_AirQuotationSlabRate.fsaqw_weight%TYPE, p_user_id IN FSAQR_AirQuotationRate.fsaqr_createdby%TYPE, p_record_status IN FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE, p_issuccess OUT FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE, p_message OUT FS_AirQuotationManagement.t_list) AS BEGIN FSAQW_AirQuotationSlabRate_PKG.put_fsaqw_prc( p_fsaqw_id => p_fsaqw_id, p_fsofo_id => p_fsofo_id, p_fsaqr_id => p_fsaqr_id, p_fsatm_id => p_fsatm_id, p_fsaqw_buyingrate => p_fsaqw_buyingrate, p_fsaqw_markupvalue => p_fsaqw_markupvalue, p_fsaqw_sellingrate => p_fsaqw_sellingrate, P_fsaqw_weight => P_fsaqw_weight, p_fsaqw_purgeyear => TO_CHAR(SYSDATE,'YYYY'), p_user_id => p_user_id, p_record_status => p_record_status, p_issuccess => p_issuccess, p_message => p_message); EXCEPTION WHEN OTHERS THEN p_issuccess := fs_constants.k_failure; fs_exception.raise_exception; END put_fsaqw_prc; END FS_AirQuotationManagement; create or replace PACKAGE /*FS-050620-01*/ FS_Country AUTHID DEFINERAS /* ----------------------------------------------------------------------------------------------------------------------------------- Modification History ---------------------------------------------------------------------------------------------------------------------------------- || Version When Who What || ------------------------------------------------------------------------------------------------------------------------------- || FS-240120-01 24-Jan-2020 Praveen Kumar.D FS_Country - This package contains procedures of Country Details.
Silicone Hose For Air Intake,
Dog Training West Chester, Pa,
Candela Laser Treatment,
Aws Consulting Partner,
First Aid Kit For Factory,
Christmas Headband Printable,
Ee Wide Fitting Dance Shoes,
